Delimited strings

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


  • 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


    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'

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

  • 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

  • I agree. Or jsut pass in the items you want to split from the deleted table as parameters.

    Steve Jones

  • There are several functions in the scripts area that do this.

  • Try this

    Simon Sabin

    Co-author of SQL Server 2000 XML Distilled

    Simon Sabin
    SQL Server MVP

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

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