Post reply

Delimited strings

  • 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

  • January 19, 2002 at 9:03 pm

    #422346

    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'

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

  • January 20, 2002 at 11:04 am

    #422349

    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

  • January 21, 2002 at 12:14 pm

    #422387

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

    Steve Jones

    steve@dkranch.net

  • December 8, 2002 at 7:03 pm

    #443676

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

  • December 9, 2002 at 7:56 am

    #443747

    Try this

    http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=528

    Simon Sabin

    Co-author of SQL Server 2000 XML Distilled

    http://www.amazon.co.uk/exec/obidos/ASIN/1904347088


    Simon Sabin
    SQL Server MVP

    http://sqlblogcasts.com/blogs/simons

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

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