Compare two columns

  • Hello,

    I have one table about 1000 rows.

    My table is from these columns - address, alias.

    Several data from my table:

    Nraddress alias

    1x1@yahoo.comX1@exc.yahoo.ex

    2X2@gmail.comX2@exc.gmail.ex,X3@exc.bcm.ex

    3X4@gmail.comX4@exc.gmail.ex,X5@exc.bbx.ex,X6@exc.bbx.ex

    4X7@co.com X8@exc.gmail.ex,X2@exc.yahoo.ex,X10@exc.coo.ex

    5X11@x.com X11@exc.x.ex

    6X12@nor.comX12@exc.nor.ex,X13@exc.nor.ex

    I would like get this result:

    Nr address alias

    1

    2X2@gmail.comX3@exc.bcm.ex

    3X4@gmail.comX5@exc.bbx.ex,X6@exc.bbx.ex

    4X7@co.com X8@exc.gmail.ex,X2@exc.yahoo.ex,X10@exc.coo.ex

    5

    6X12@nor.comX13@exc.nor.ex

    I have attached excel file with data.

    Can somebody help me for it?

    Thank you very much.

  • How do you get from your data to your result? I see things not reported but why?

  • 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/[/url]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.

  • 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?

  • 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

  • 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.

  • 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/[/url]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.

  • 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

  • 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/[/url]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.

  • 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

  • Myke85 (8/21/2014)


    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

    You can't. The LIKE comparison doesn't work that way. PATINDEX and CHARINDEX are a better choice in this case.

    I'll see what I can work up as a solution to this issue, but it won't be until tomorrow morning earliest. In the meantime, if someone else has a solution, I'm sure they will post.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]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.

  • 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 @email2 like '%' + @email1 + '%' then 'good' else 'false' end as Result

  • lpablo (8/22/2014)


    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 @email2 like '%' + @email1 + '%' then 'good' else 'false' end as Result

    FYI: This code has obviously not been tested.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]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.

  • Ok, i missed the variable name "@email".

    I'm Sorry and, no, i didn't test it

  • Brandie Tarvin (8/22/2014)


    lpablo (8/22/2014)


    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 @email2 like '%' + @email1 + '%' then 'good' else 'false' end as Result

    FYI: This code has obviously not been tested.

    But if you replace @email1 with @email it will work. 🙂

Viewing 15 posts - 1 through 15 (of 29 total)

You must be logged in to reply to this topic. Login to reply