Delimited strings

Question

Post reply

Delimited strings

julliff

SSChasing Mays

Points: 646
More actions
January 19, 2002 at 2:03 pm

#77929

Does anyone know of a neat way to split a delimoted string into variables in T-SQL, a bit like VB Split function?

Thanks

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

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

Leon Platt SSCommitted Points: 1921 More actions · Answer 1

Nothing neat that I know of

Just raw string manipulation:

declare @string as varchar(50)

declare @strLen as int

declare @delimPos as int

set @string='new york,florida,california'

set @strLen=len(@string)

set @delimPos = charindex(',',@string)

select left(@string,@delimPos-1)

set @string=substring(@string,@delimPos+1,@strLen)

set @delimPos = charindex(',',@string)

select left(@string,@delimPos-1)

set @string=substring(@string,@delimPos+1,@strLen)

set @delimPos = charindex(',',@string)

if @delimPos=0

select @string

else

select left(@string,@delimPos-1)

Although I guess you could be slick by counting the delimeters and create a temp table with that many fields and then do a INSERT INTO tempTable values (' + delimeted string + '). You would also have to convert your delimeters to 'delimeter'

jwiner SSCrazy Points: 2241 More actions · Answer 2

jwiner

SSCrazy

Points: 2241

January 20, 2002 at 9:52 am

#422348

Stored procs are generally not the best places to do string manipulation. SQL is not optimized for it. Why are you using this method? Just curious, maybe we can offer another solution.

julliff SSChasing Mays Points: 646 More actions · Answer 3

Ok,here it is..

Within an update trigger I want to call a stored proc to write one, maybe two audit records. The first audit table has just one field in which to record the recordID. Since there may be a composite key (at the moment the maximum is 4, I have concatenated the values into a delimited string (they are all varchar 16s. The second record is a full audit record, which writes the whole of the original record to full audit record. I think I have just talked myself out of this one!! The easiest way would be to have two stored procedures, which fire from the trigger,which would both have access to the deleted table