Two Strings compare using t-sql

  • Jus

    SSCrazy

    Points: 2530

    Declare @Intstr1 varchar(100)

    Declare @Intstr2 varchar(100)

    Set @Intstr1 = '1,2,3,4,5,6,7,8,9'   -- This will NOT contain duplicates

    Set @Intstr2 = '4,6,6,7,7'  -- This string may have duplicates but surely exists in str1

    Need a t-sql for finding numbers from Str1 which are NOT in Str2

    Please help.

    Expected output..

    @output = '1,2,3,5,8,9'

     

     

    • This topic was modified 3 months, 1 week ago by  Jus.
    • This topic was modified 3 months, 1 week ago by  Jus.
    • This topic was modified 3 months, 1 week ago by  Jus.
    • This topic was modified 3 months, 1 week ago by  Jus.
  • Lynn Pettis

    SSC Guru

    Points: 442359

    Out of curiosity, what have you tried to solve this problem?

    I ask because I already see one possible solution.

  • Jus

    SSCrazy

    Points: 2530

    I tried using loops  which I know not a better way. Is there any solution with direct sql query to get required output.

  • Jeff Moden

    SSC Guru

    Points: 997128

     SELECT STRING_AGG(d.value,',')
    FROM (
    SELECT value
    FROM STRING_SPLIT(@Intstr1,',')
    EXCEPT
    SELECT value
    FROM STRING_SPLIT(@Intstr2,',')
    ) d
    ;

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jus

    SSCrazy

    Points: 2530

    Worked like a champ. Thanks jeff.

  • Jeff Moden

    SSC Guru

    Points: 997128

    Jus wrote:

    Worked like a champ. Thanks jeff.

    😀  Thanks for the feedback.

    To be sure, though, do you understand how and why it works?

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • schleep

    SSChampion

    Points: 12551

    Wow. So simple now.

    Still labouring in 2008, our next version is supposed to be 2016, with no set date for that.

    I figure I'll be able to start using STRING_AGG sometime around 2028 - when we upgrade to 2019 :-\

  • Jeff Moden

    SSC Guru

    Points: 997128

    schleep wrote:

    Wow. So simple now.

    Still labouring in 2008, our next version is supposed to be 2016, with no set date for that.

    I figure I'll be able to start using STRING_AGG sometime around 2028 - when we upgrade to 2019 :-\

    Heh... been there, done that.  Not fun.

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 8 posts - 1 through 8 (of 8 total)

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