Alex Grinberg-230981 (8/13/2010)
INSTALL function (code below) and use it to split a valueCREATE FUNCTION [dbo].[StringToTable]
(@RawString nvarchar(4000), @Delimiter nvarchar(10) )
RETURNS
@VList TABLE (RowID int IDENTITY(1,1),SplitedValue varchar(200))
AS
BEGIN
WITH CSVCte (StartPos, EndPos) AS
(SELECT 1 AS StartPos, CHARINDEX(@Delimiter , @RawString + @Delimiter) AS EndPos
UNION ALL
SELECT EndPos + 1 AS StartPos , CHARINDEX(@Delimiter,@RawString + @Delimiter , EndPos + 1) AS EndPos
FROM CSVCTE WHERE CHARINDEX(@Delimiter, @RawString + @Delimiter, EndPos + 1) <> 0)
INSERT INTO @VList
SELECT SUBSTRING(@RawString, StartPos,EndPos - StartPos) FROM CSVCte
RETURN
END
GO
-- sample call: select SplitedValue from dbo.StringToTable('A,B,C,D,E,F,G', ',')
That's a different take on using a recursive CTE to do it. I'll have to check the performance, though, because recursive CTE's tend to be nothing more than "Hidden RBAR" with all the performance problems associated with RBAR.
--Jeff Moden
Change is inevitable... Change for the better is not.