Parse & Split Comma Limited (CSV) List IN SQL Server

Parse & Split Comma Limited (CSV) List IN SQL Server

-- Split comma-limited number list with the charindex function -- Table-valued user-defined function CREATE FUNCTION dbo.fnSplitCSV ( @NumberList varchar(4096)) RETURNS @SplitList TABLE ( ListMember INT ) AS BEGIN DECLARE @Pointer int, @ListMember varchar(25) SET @NumberList = LTRIM(RTRIM(@NumberList)) IF (RIGHT(@NumberList, 1) != ',') SET @NumberList=@NumberList+ ',' SET @Pointer = CHARINDEX(',', @NumberList, 1) IF REPLACE(@NumberList, ',', '') <> '' BEGIN WHILE (@Pointer > 0) BEGIN SET @ListMember = LTRIM(RTRIM(LEFT(@NumberList, @Pointer - 1))) IF (@ListMember <> '') INSERT INTO @SplitList VALUES (convert(int,@ListMember)) SET @NumberList = RIGHT(@NumberList, LEN(@NumberList) - @Pointer) SET @Pointer = CHARINDEX(',', @NumberList, 1) END END RETURN END GO -- Test SELECT * FROM dbo.fnSplitCSV ('') SELECT * FROM dbo.fnSplitCSV ('1000') SELECT * FROM dbo.fnSplitCSV ('1000,4005') SELECT * FROM dbo.fnSplitCSV ('1000,7,9') SELECT * FROM dbo.fnSplitCSV ('1000, 3, 8494, 2329992, 8, 23, 43') GO -- XML split solution for comma-limited number list -- Table-valued user-defined function CREATE FUNCTION dbo.fnSplitCSVxml (@NumberList VARCHAR(4096)) RETURNS @SplitList TABLE( ListMember INT) AS BEGIN DECLARE @xml XML SET @NumberList = LTRIM(RTRIM(@NumberList)) IF LEN(@NumberList) = 0 RETURN SET @xml = '' + REPLACE(@NumberList,',','') + '' INSERT INTO @SplitList SELECT x.i.value('.','VARCHAR(MAX)') AS Member FROM @xml.nodes('//n') x(i) RETURN END GO -- Test SELECT * FROM dbo.fnSplitCSVxml ('') SELECT * FROM dbo.fnSplitCSVxml ('1000') SELECT * FROM dbo.fnSplitCSVxml ('1000, 1007') SELECT * FROM dbo.fnSplitCSVxml ('1000,7,9') SELECT * FROM dbo.fnSplitCSVxml ('1000, 3, 8494, 2329992, 8, 23, 43') GO -- Split a comma-limited string list -- Table-valued user-defined function CREATE FUNCTION dbo.fnSplitStringList (@StringList VARCHAR(MAX)) RETURNS @TableList TABLE( StringLiteral VARCHAR(128)) BEGIN DECLARE @StartPointer INT, @EndPointer INT SELECT @StartPointer = 1, @EndPointer = CHARINDEX(',', @StringList) WHILE (@StartPointer < LEN(@StringList) + 1) BEGIN IF @EndPointer = 0 SET @EndPointer = LEN(@StringList) + 1 INSERT INTO @TableList (StringLiteral) VALUES(LTRIM(RTRIM(SUBSTRING(@StringList, @StartPointer, @EndPointer - @StartPointer)))) SET @StartPointer = @EndPointer + 1 SET @EndPointer = CHARINDEX(',', @StringList, @StartPointer) END -- WHILE RETURN END -- FUNCTION GO -- Test SELECT * FROM dbo.fnSplitStringList ('New York, California, Arizona, Texas') GO SELECT * FROM dbo.fnSplitStringList ('New York, California, Arizona, Texas,') GO SELECT * FROM dbo.fnSplitStringList ('New York') SELECT * FROM dbo.fnSplitStringList ('Smith, Brown, O''Brien, Sinatra') GO