mo.notono.us

Monday, August 29, 2005

SQL: Splitting a Text Value Into a Table

...And here is the SplitText function modified to use the NumberTable function:

--Splits a delimited text into individual items
--From http://www.sommarskog.se/arrays-in-sql.html#tblnum-unlimited
CREATE FUNCTION dbo.SplitText(
  @List  ntext,
  @Del nchar(1) = N','
)
RETURNS @T TABLE (Item nvarchar(4000)) 
AS
BEGIN
  DECLARE @slices TABLE (slice nvarchar(4000) NOT NULL)
  DECLARE @slice nvarchar(4000), @Textpos int, @maxlen int, @stoppos int, @maxnum int
  
  SELECT @Textpos = 1, @maxlen = 4000 - 2, @maxnum = DATALENGTH(@List) / 2
  WHILE DATALENGTH(@List) / 2 - (@Textpos - 1) >= @maxlen
  BEGIN
     SELECT @slice = SUBSTRING(@List, @Textpos, @maxlen)
     SELECT @stoppos = @maxlen - CHARINDEX(@Del, REVERSE(@slice))
     INSERT @slices (slice) VALUES (@Del + LEFT(@slice, @stoppos) + @Del)
     SELECT @Textpos = @Textpos - 1 + @stoppos + 2   -- On the other side of the delimiter.
  END
  INSERT @slices (slice) VALUES (@Del + SUBSTRING(@List, @Textpos, @maxlen) + @Del)
  
  INSERT @T (Item)
  SELECT x.Item
  FROM (
    SELECT Item = LTRIM(RTRIM(SUBSTRING(s.slice, N.Number + 1, 
      CHARINDEX(@Del, s.slice, N.Number + 1) - N.Number - 1)))
    FROM dbo.NumberTable(1, @maxnum) N
      INNER JOIN @slices s ON N.Number <= LEN(s.slice) - 1
        AND SUBSTRING(s.slice, N.Number, 1) = @Del
  ) AS x
  
  RETURN
END

Labels:

0 Comments:

Post a Comment

Links to this post:

Create a Link

<< Home