Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10

    Unanswered: Parse Delimited String

    A colleague of mine approached me today and asked the age old question about parsing a delimited string in SQL...

    I have already suggest a more appropriate solution to the problem he was having, but my intrigue didn't stop there and I decided to write a UDF to parse the string anyway (partly because there was an existing method that looped on the production box).

    So here's what I came up with, which I'm putting out there for comments and criticism. I can't think of a more efficient method, but if you can please let me know.

    Note that because of the use of CTE this is will only work in 2005 onwards.
    Code:
    CREATE FUNCTION dbo.parse_delimited_integers (
       @string    varchar(Max)
     , @delimiter varchar(2)
    )
      RETURNS @values table (
         value int
      )
    AS
      BEGIN
        ; WITH cte AS (
          SELECT 0 As [pos]
               , 0 As [level]
            UNION ALL
              SELECT Convert(int, CharIndex(@delimiter, @string, cte.pos + 1))
                   , level + 1
              FROM   cte
              WHERE  CharIndex(@delimiter, @string, cte.pos + 1) > 0
        )
    
        INSERT INTO @values (value)
        SELECT Convert(int, SubString(@string, a.pos + 1, Coalesce(b.pos + 1, Len(@string)) - a.pos - 1)) As [value]  
        FROM   cte a
         LEFT
          JOIN cte b
            ON a.level + 1 = b.level
    
        RETURN
      END
    GO
    
    SELECT *
    FROM   dbo.parse_delimited_integers('12, 123, 123526, -325, 4636, 34634634', ', ')
    GO
    
    GO
    --DROP FUNCTION dbo.parse_delimited_integers
    This particular function returns a table of integer values - this can easily be changed to cater for character datatypes if necessary.

    I look forward to reading any feedback

    EDIT: Addition of +1 for those values which don't contain a delimiter.
    Last edited by gvee; 12-10-08 at 08:41.
    George
    Home | Blog

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    not for nuthin...but if it's a delimited file, I'd probably bcp it in

    even if it was only 1 line

    I've also used xml extensions to do that as well though in sprocs
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    In this scenario it was a developer wanting to pass a comma separated list of values to a SQL function and wondering why IN wasn't working
    George
    Home | Blog

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Mhmm!
    If I get the time I'll perf test that version against this one too.
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •