Search This Blog

Splitting CSV string using CTE

DECLARE @CSVString varchar(2000)
DECLARE @Delimiter varchar(1)
SELECT 
      @CSVString = 'This,is,a,test,for,splitting,using,a,CTE,to,break,a,varchar,into,records,based,on,a,delimiter',
      @Delimiter = ','
SET @CSVString = @CSVString+@Delimiter  --- append the delimiter 
;WITH Res(s, r)
AS
(
SELECT
SUBSTRING(@CSVString,1, CHARINDEX(@Delimiter, @CSVString)-1) s,
SUBSTRING(@CSVString,CHARINDEX(@Delimiter, @CSVString)+1, len(@CSVString)) r
UNION ALL
SELECT
SUBSTRING(r,1, CHARINDEX(@Delimiter, r)-1) s,
SUBSTRING(r,CHARINDEX(@Delimiter, r)+1, len(r)) r
FROM Res
WHERE
CHARINDEX(@Delimiter, r) > 0
)
SELECT s FROM Res

No comments: