string manipulation

Question

Post reply

string manipulation

jondy87

SSC-Addicted

Points: 426
More actions
November 11, 2010 at 10:26 am

#228962

HI everyone,
I have a issue
If the input string = '1001,2001,1002,2002,1003,2003'
I need the result as
@id1 = '1001,1002,1003'
@id2 = '2001,2002,2003'
Any ideas
thankyou

Viewing 4 posts - 1 through 3 (of 3 total)

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

WayneS SSC Guru Points: 95460 More actions · Answer 1

declare @s-2 varchar(100) = '1001,2001,1002,2002,1003,2003';

declare @id1 varchar(100);

declare @id2 varchar(100);

set @id1 = STUFF((SELECT ',' + Item

FROM dbo.DelimitedSplit8K(@s, ',')

WHERE ItemNumber % 2 = 1 -- odd numbers

ORDER BY ItemNumber

FOR XML PATH(''), TYPE).value('.','varchar(max)'),1,1,'');

set @id2 = STUFF((SELECT ',' + Item

FROM dbo.DelimitedSplit8K(@s, ',')

WHERE ItemNumber % 2 = 0 -- even numbers

ORDER BY ItemNumber

FOR XML PATH(''), TYPE).value('.','varchar(max)'),1,1,'');

select @id1, @id2;

Click here for the latest Delimited Split Function.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

GSquared SSC Guru Points: 260824 More actions · Answer 2

Beat me to it! Was about to post almost the same thing.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

jondy87 SSC-Addicted Points: 426 More actions · Answer 3

Thanks alot WayneS n Gsquared

I will convert this as a function for my issue.