Quick T-Sql to parse a delimited string

I was working on some code to parse some data for a friend, and had
to write this little piece of code that some of you might find
useful.  Right now, you can just take this, pop it into query
analyzer and run it.  Its simple enough to change this to a
function that accepts the string, plus a delimeter value, such as:

CREATE FUNCTION dbo.ParseString (@string varchar(500), @delimeter char(1))
RETURNS table

Or do the same type thing using a stored procedure, whatever floats
your boat.  You’ll juse have to make some small modifications to
the code.

Here is the code:

Parse a comma-delimeted string

– This would be the input parameter of the stored procedure, if you want to do it that way, or a UDF
declare @string varchar(500)
set @string = ‘ABC,DEF,GHIJK,LMNOPQRS,T,UV,WXY,Z’

declare @pos int
declare @piece varchar(500)

– Need to tack a delimiter onto the end of the input string if one doesn’t exist
if right(rtrim(@string),1) <> ‘,’
 set @string = @string  + ‘,’

set @pos =  patindex(‘%,%’ , @string)
while @pos <> 0
begin
 set @piece = left(@string, @pos – 1)
 
 – You have a piece of data, so insert it, print it, do whatever you want to with it.
 print cast(@piece as varchar(500))

 set @string = stuff(@string, 1, @pos, ”)
 set @pos =  patindex(‘%,%’ , @string)
end

The above code outputs:

ABC
DEF
GHIJK
LMNOPQRS
T
UV
WXY
Z

This entry was posted in Sql Development. Bookmark the permalink. Follow any comments here with the RSS feed for this post.

29 Responses to Quick T-Sql to parse a delimited string

  1. dq0819 says:

    You can try the following approach using XML. However still on works for SQL Server 2008:

    CREATE FUNCTION dbo.fnToDelimitedTable(@Value varchar(8000), @Delimiter nvarchar(1) = ‘,’)
    RETURNS @TableValues table
    (
    DataRow nvarchar(4000)
    ,Id bigint identity(1,1))
    AS
    BEGIN
    DECLARE @data xml
    SET @Value = REPLACE(@Value, @Delimiter, ‘‘)
    SET @Value = ‘‘ + @Value + ‘
    SET @data = @Value
    INSERT @TableValues (DataRow)
    SELECT
    DataRows.DataRow.value(‘/DataRows[1]/DataRow[1]‘, ‘nvarchar(4000)’)
    FROM @data.nodes(‘DataRows[1]/DataRow’) DataRows(DataRow)

    RETURN
    END
    GO

  2. test says:

    who cares

  3. Nice captcha says:

    Nice captcha

  4. Brian Rubin says:

    Worked great! Thanks!

  5. Yusuf says:

    I like it. Easy and smart for short strings!
    Thanks alot…

  6. Batu says:

    Thank you, just what i was looking for.

  7. Dave says:

    Kudo’s
    Thanks for the simple solution.

  8. Adam Mikolaj says:

    Totally awesome. I threw this into a cursor and was able to parse a whole table 1 row at a time. Saved me so much time.

  9. Mike Maholchic says:

    Just what I needed to get the file name from a filename and path, thanks

  10. humair says:

    thanks alot this worked for me

  11. Raj says:

    Raymond — Thanks!
    This is just what I was looking for; easy to understand, and effective.

    Since I’ll be using this for Sybase ASE 12.5, the only change I might have to make is:

    From: set @string = stuff(@string, 1, @pos, ”)
    To: set @string = stuff(@string, 1, @pos, null)

    Per Sybase 12.5:
    “Adaptive Server converts an empty string constant to a string of 1 space”

    Thanks again!
    Raj

  12. Sebastian says:

    i took some of your code and i made my own function i hope somebady like it.

    CREATE FUNCTION Split(
    @IDValues as varchar(500),
    @Delimeter as varchar(1)
    )
    RETURNS @tValues TABLE(IDAux INT)
    AS
    BEGIN
    DECLARE @I AS INT, @Aux AS INT

    SET @I = 1
    SET @Aux = 1
    WHILE @I <= LEN(@IDValues)
    BEGIN
    IF SUBSTRING(@IDValues,@I,1)= @Delimeter
    BEGIN
    INSERT INTO @tValues (IDAux) VALUES (CAST(SUBSTRING(@IDValues,@Aux,(@I-@Aux))AS INT))
    SET @Aux = @i + 1
    END
    SET @i = @i + 1
    END
    RETURN
    END

    GO

    –examples with differents delimeters

    select * from Split(’1058,1059,1060,1062,’,',’)
    select * from Split(’1058|1059|1060|1062|’,'|’)
    select * from Split(’1058;1059;1060;1062;’,';’)

  13. Babak Mokri says:

    Thank you very much it work just fine.

  14. Ryan says:

    Thanks. I had code that I wrote in the past that did this very thing, but I was in a crunch and didn’t have time to either find my old code or write it again from scratch. Thanks for saving me the time!

  15. Raj says:

    Thanks. Worked for me.

  16. Rod says:

    Thanks.

  17. MikeJohnson says:

    Good stuff.
    Thanks!

  18. Tripod says:

    Great stuff but now I need to get the pased results into their own variables. Of course, I don’t have an infinite number of parts in the string-to-parse. Here are six example strings to be parsed that has 0 – 5 parts:

    1. null
    2. ABC
    3. ABC-123
    4. ABC-123-DEF
    5. ABC-123-DEF-789
    6. ABC-123-DEF-789-XYZ

    I would like to get the results into variables such as:

    @v_parsed_1
    @v_parsed_2
    @v_parsed_3
    @v_parsed_4
    @v_parsed_5

    where, in the 4th example above, would result in:

    @v_parsed_1 = ‘ABC’
    @v_parsed_2 = ’123′
    @v_parsed_3 = ‘DEF’
    @v_parsed_4 = null
    @v_parsed_5 = null

    Thanks in advance!

  19. Tripod says:

    Please provide an example of how one would use this function to set variables for each of the parsed values. For example, if the string to parse was “ABC-123-DEF” how would we get the values into their own variables? For my requirements, there will be a constant maximum number of parts to parse from the string; not infinite. That value is 4 and the logic should handle these string examples:

    (null – no value passed)
    ABC
    ABC-123
    ABC-123-DEC
    ABC-123-DEC-XYZ

    Thanks in advance!

  20. neven says:

    Many thanks!

  21. Bruce H says:

    Thank you.

    Just what I was looking for.

    Great job.

    B

  22. steve says:

    Thanks, this worked for me to parse my postcodes.

  23. John D says:

    …and just what I needed as well. Thanks!

  24. thorkyl says:

    Thanks, just what I needed….

  25. chris says:

    old post, but i just stole your code. works perfectly – patindex or not.

  26. You may also want to have a look at Erland Sommarskog’s article at http://www.sommarskog.se/arrays-in-sql.html.

    I took his code and tweaked it some, dbo.Numbers is a simple standalone table with numbers from 1 to 1 million.

    –Fast Split function using a sequence table (dbo.Numbers)
    –Created 08/29/05 by Oskar Austegard from Erland Sommarskog’s code at
    –http://www.sommarskog.se/arrays-in-sql.html#tblnum-core
    CREATE FUNCTION Split (
    @List varchar(7998), –The delimited list
    @Del char(1) = ‘,’ –The delimiter
    )
    RETURNS @T TABLE (Item varchar(7998))
    AS
    BEGIN
    DECLARE @WrappedList varchar(8000), @MaxItems int
    SELECT @WrappedList = @Del + @List + @Del, @MaxItems = LEN(@List)

    INSERT INTO @T (Item)
    SELECT SUBSTRING(@WrappedList, Number + 1, CHARINDEX(@Del, @WrappedList, Number + 1) – Number – 1)
    FROM dbo.Numbers n
    WHERE n.Number <= LEN(@WrappedList) – 1
    AND SUBSTRING(@WrappedList, n.Number, 1) = @Del

    RETURN
    END

  27. David,

    I’m just partial to using PATINDEX because I’m used to using wildcard characters in my search strings, which CHARINDEX does not support. You’re way works just fine too in this exact scenario.

    However, with PATINDEX, you can use the delimeter of “,_,”, which would exclude single char pieces of data from your result set. You cannot do this with CHARINDEX.

    This following piece of code shows the value of using PATINDEX

    declare @a varchar(100)
    set @a = ‘abcdefgh,ijklm,n,opqrs,tuv’
    print patindex(‘%,_,%’,@a)

    Will print ’15′.

  28. Hi Raymond,

    Don`t want to be picky here (or do I ? :-), but is there any reason why you use patindex?

    This works too:

    – [...]

    set @pos = charindex(‘,’ , @string)
    while @pos <> 0
    begin
    set @piece = left(@string, @pos – 1)
    print cast(@piece as varchar(500))
    set @string = stuff(@string, 1, @pos, ”)
    set @pos = charindex(‘,’ , @string)
    end

Leave a Reply