Function to Split a Delimited String into a Table

  • Andy B.

    SSC Eights!

    Points: 890

    Comments posted to this topic are about the item Function to Split a Delimited String into a Table


    Enjoy!

    A Brown

    Manage all of your backups in one simple job using this script.

  • philcart

    SSC-Forever

    Points: 47794

    SQL 2005 variant that doesn't use a loop.

    http://philcart.blogspot.com/2007/06/split-function.html

    Could be easily modified to work with nvarchar(4000)

    --------------------
    Colt 45 - the original point and click interface

  • Stylez

    SSCrazy

    Points: 2195

    Hi good Sir

    thanks for doing the string split function. i would like to kindly ask if you have a sample code for my scenario below;

    Column 1 = ID

    Column 2 = Lastname

    Column 3 = Job Description

    and then i will declare a variable to be used in inserting the columns mentioned above

    declare @Value varchar(4000)

    set @value = 'IDValue,Lastnamevalue,JobDescriptionvalue'

    i noticed that you are inserting these values in a single column. is it possible to save these values in a seperate column named ID,Lastname and Job Description? your assistance is very much appreciated.

    Best Regards

    N.O.E.L.

  • g.chakhidze

    Grasshopper

    Points: 17

    Hi,

    Thank you, I've found this function very useful.

    There is a little typo at the line

    SET @list = LTRIM(RTRIM(@list)) + ','

    Instead of concatenating with default value (,), you must concatenate with passed in @delimiter argument.

    SET @list = LTRIM(RTRIM(@list)) + @delimiter

    Without this, if you use delimiter different than comma, it will not add last row to the resulting table.

    For example:

    select * from DStringToTable(N'one|two', N'|')

    returns table

    value

    -----

    one

    which is missing value 'two'

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

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