Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Compare two columns Expand / Collapse
Author
Message
Posted Friday, August 15, 2014 3:20 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: 2 days ago @ 11:28 PM
Points: 16, Visits: 85
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.


  Post Attachments 
Book1.xlsx (15 views, 14.45 KB)
Post #1603617
Posted Friday, August 15, 2014 5:16 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 10:52 AM
Points: 266, Visits: 854
How do you get from your data to your result? I see things not reported but why?
Post #1603639
Posted Friday, August 15, 2014 12:01 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 12:40 PM
Points: 7,210, Visits: 6,352
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 Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1603811
Posted Sunday, August 17, 2014 11:13 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: 2 days ago @ 11:28 PM
Points: 16, Visits: 85
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?
Post #1604212
Posted Sunday, August 17, 2014 12:38 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 8:47 AM
Points: 8,832, Visits: 9,389
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
Post #1604213
Posted Sunday, August 17, 2014 4:44 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: 2 days ago @ 11:28 PM
Points: 16, Visits: 85
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.
Post #1604229
Posted Monday, August 18, 2014 4:22 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 12:40 PM
Points: 7,210, Visits: 6,352
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 Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1604343
Posted Monday, August 18, 2014 4:58 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 8:47 AM
Points: 8,832, Visits: 9,389
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
Post #1604720
Posted Tuesday, August 19, 2014 4:17 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 12:40 PM
Points: 7,210, Visits: 6,352
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 Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1604824
Posted Thursday, August 21, 2014 12:34 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: 2 days ago @ 11:28 PM
Points: 16, Visits: 85
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

Post #1605962
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse