dimitri.decoene-1027745 (2/25/2011)
Another way to do it is by using the Tally table. Check out Jeff Moden's article http://www.sqlservercentral.com/articles/T-SQL/62867/.Here's some example code (how i use it on our systems):
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[udfTransformStringToColumn] (
@StringVARCHAR(MAX),
@DelimiterVARCHAR(1)
)
RETURNS @List TABLE (ValueColumn VARCHAR(255) NULL)
AS
BEGIN
IF LEFT(@String, 1) <> @Delimiter SET @String = @Delimiter + @String
IF RIGHT(@String, 1) <> @Delimiter SET @String = @String + @Delimiter
;WITH
cteTally AS
(
SELECTTOP (LEN(@String))
ROW_NUMBER() OVER (ORDER BY t1.Object_ID) AS N
FROMMaster.sys.All_Columns t1
CROSS JOIN Master.sys.All_Columns t2
)INSERT INTO @List (ValueColumn)
SELECTSUBSTRING(@String,N+1,CHARINDEX(@Delimiter,@String,N+1)-N-1) AS Value
FROMcteTally
WHEREN < LEN(@String)
AND SUBSTRING(@String,N,1) = @Delimiter
RETURN
END
GO
Interesting function. However, you may want to always append a delimiter to the string because the function would not return the correct number of beginning empty strings. Change
IF LEFT(@String, 1) <> @Delimiter SET @String = @Delimiter + @String
TO
SET @String = @Delimiter + @String
SELECT *
FROM [dbo].[udfTransformStringToColumn]( ';;;Token1;Token2;Token3;Token4;Token5;;', ';' )
The beginning ";;;" of the string should return 3 blank rows, however, the function returns 2.