Post a Comment On: mo.notono.us

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