SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Compare two columns


Compare two columns

Author
Message
Myke85
Myke85
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 102
Hello,

I have one table about 1000 rows.
My table is from these columns - address, alias.
Several data from my table:


Nr address alias
1 x1@yahoo.com X1@exc.yahoo.ex
2 X2@gmail.com X2@exc.gmail.ex,X3@exc.bcm.ex
3 X4@gmail.com X4@exc.gmail.ex,X5@exc.bbx.ex,X6@exc.bbx.ex
4 X7@co.com X8@exc.gmail.ex,X2@exc.yahoo.ex,X10@exc.coo.ex
5 X11@x.com X11@exc.x.ex
6 X12@nor.com X12@exc.nor.ex,X13@exc.nor.ex



I would like get this result:


Nr address alias
1
2 X2@gmail.com X3@exc.bcm.ex
3 X4@gmail.com X5@exc.bbx.ex,X6@exc.bbx.ex
4 X7@co.com X8@exc.gmail.ex,X2@exc.yahoo.ex,X10@exc.coo.ex
5
6 X12@nor.com X13@exc.nor.ex


I have attached excel file with data.
Can somebody help me for it?
Thank you very much.
Attachments
Book1.xlsx (20 views, 14.00 KB)
Dana
Dana
SSCertifiable
SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)

Group: General Forum Members
Points: 5910 Visits: 3450
How do you get from your data to your result? I see things not reported but why?
Brandie Tarvin
Brandie Tarvin
SSC-Dedicated
SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)

Group: General Forum Members
Points: 37697 Visits: 9268
I'm not even seeing the logic between the 3 columns of the original data and the rows that appear to have more than three values.

Please post your table DDL and an INSERT statement for your data so we can see how the data fits in these columns. Then post an english language version (as opposed to a code version) of what you are trying to achieve with the results.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Myke85
Myke85
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 102
Hello,

I have added to declare table:



declare @info table ( nr int, [address] varchar(100), alias varchar(100))
INSERT INTO @info (nr, [address],alias)
Select 1,'x1@yahoo.com','X1@exc.yahoo.ex'
union all
Select 2,'X2@gmail.com','X2@exc.gmail.ex,X3@exc.bcm.ex'
union all
Select 3,'X4@gmail.com','X4@exc.gmail.ex,X5@exc.bbx.ex,X6@exc.bbx.ex'
union all
Select 4, 'X7@co.com', 'X8@exc.gmail.ex,X2@exc.yahoo.ex,X10@exc.coo.ex'
union all
Select 5,'X11@x.com', 'X11@exc.x.ex'
union all
Select 6,'X12@nor.com','X12@exc.nor.ex,X13@exc.nor.ex'


I would like get this result:


declare @result table ( nr int, [address] varchar(100), alias varchar(100))
INSERT INTO @result (nr, [address],alias)
Select 1,'', ''
union all
Select 2,'X2@gmail.com','X3@exc.bcm.ex'
union all
Select 3,'X4@gmail.com','X5@exc.bbx.ex,X6@exc.bbx.ex'
union all
Select 4,'X7@co.com','X8@exc.gmail.ex,X2@exc.yahoo.ex,X10@exc.coo.ex'
union all
Select 5,'',''
union all
Select 6,'X12@nor.com','X13@exc.nor.ex'

Select * from @result


What should I use a query for this result?
Tom Thomson
Tom Thomson
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25913 Visits: 12494
You seem to be looking for cases with more than 1 address alias, which can be detected by having a comma (",") in the alias column.

So maybe this will work:

 SELECT nr
, case when alias not like '%,%' then NULL else [address] end
, case when alias not like '%,%' then NULL else alias end
from @info



That's not very efficient, but your info table isn't even in 1st normal form and efficiency is not going to happen when you start off with a table which is disastrously deficient in normalisation.

Tom

Myke85
Myke85
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 102
Thank you for the simple query.
Yes,the table alias column has more than 1 value.
I would like to compare address column with alias column.
If address column is equal alias column then alias column is as NULL if not than it leave the current alias value.
Brandie Tarvin
Brandie Tarvin
SSC-Dedicated
SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)

Group: General Forum Members
Points: 37697 Visits: 9268
So did Tom answer your question, then?

If so, please mark his post as the solution. If not, please post again and tell us why it didn't work for you.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Tom Thomson
Tom Thomson
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25913 Visits: 12494
Brandie Tarvin (8/18/2014)
So did Tom answer your question, then?

If so, please mark his post as the solution. If not, please post again and tell us why it didn't work for you.

No, my post doesn't answer it completely - it doesn't handle the com to ex match, and it doesn't handle removing a match from a multi-entry alias, and it doesn't handle a single entry alias which doesn't match (with ex for com) the address.

I don't have time just now, maybe if no-one else suggests anything before late wednesday (when I may have some spare time) I'll do something.

But maybe Myke85 can do it: needs to look up "replace" and use it several times in the query (for getting a string with ex from a string with com to see if there's a match, and then for replacing a string that matches and its delimiting comma by an empty string in an alias with more than one address in it).

Tom

Brandie Tarvin
Brandie Tarvin
SSC-Dedicated
SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)

Group: General Forum Members
Points: 37697 Visits: 9268
My problem is that he still hasn't explained in English what he expects the result to be. I told him specifically NOT to explain it in code and that's what he did... Write code.

So without understanding his logic behind his expected solution, I'm not even going to try to figure out the answer. The coded result just doesn't make sense to me given the data he provided.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Myke85
Myke85
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 102
How I can get 'good'?



DECLARE @Email VARCHAR(100)
DECLARE @Email2 VARCHAR(100)
Set @email= 'helper@sql-server-helper.com'
set @email2='fff@dd.com, helper@sql-server-helper.com,vc@.com'
SELECT case when @email like @email2 then 'good' else 'false' end as Result


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search