Two Strings compare using t-sql

Question

Post reply

Two Strings compare using t-sql

Jus

SSCrazy

Points: 2536
More actions
June 13, 2020 at 7:41 pm

#3762878
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 4 years, 5 months ago by Jus.
- This topic was modified 4 years, 5 months ago by Jus.
- This topic was modified 4 years, 5 months ago by Jus.
- This topic was modified 4 years, 5 months ago by Jus.

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

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

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

I ask because I already see one possible solution.

Jus SSCrazy Points: 2536 More actions · Answer 2

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: 1004323 More actions · Answer 3

 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.

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

Jus SSCrazy Points: 2536 More actions · Answer 4

Jus

SSCrazy

Points: 2536

June 14, 2020 at 1:43 am

#3762899

Worked like a champ. Thanks jeff.

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 5

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.

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

schleep SSChampion Points: 12585 More actions · Answer 6

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: 1004323 More actions · Answer 7

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.

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