Comma separated list to table in SQL Server

CSV to table in SQL Server

While most language APIs offer easy, out of the box ways to split a string into an array of values, the lack of any common core library in T-SQL doesn't quite make this easy. Moreover, passing an array of values to an SP in SQL Server isn't quite as streamlined as it could possibly be, requiring a custom type to be declared. Not to mention, that this feature wasn't even available until SQL Server 2008. Another method of passing a collection of values to an SP that I've seen developers use is via XML, but the syntax can be cumbersome and, judging by the execution plans that such queries produce, the performance isn't stellar. In this post I offer a function I wrote a while ago to be used in production environment. It has served its purpose well. With it, converting a string of delimiter-separated string of values into a table is quite straightforward - pass the varchar to be split as the parameter, specify the delimiter and, optionally, indicate if each value should be split. The output of the function is a two-column table, each row having a number and a value. Please see the source code below.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[TableFromString]
(
	@String varchar(max),	--Comma-separated list of values
	@Separator varchar(10),
	@Trim bit = 1 --if set to 1, each value will be stripped of trailing spaces
)
RETURNS @Table TABLE
(
	RowNumber int,
	RowValue sql_variant
)
AS
BEGIN
	if @String is null
	begin
		return
	end

	-- Fill the table variable with the rows for your result set
	--Operational variables
	declare @SeparatorIndex int,
	@StartIndex int,
	@Val nvarchar(100),
	@StringLen int,
	@RowNumber int,
	@PrevIndex int,
	@BackTrackIndex int,
	@Break bit

	set @Break = 0
	set @StartIndex = 1
	set @PrevIndex = 1
	set @StringLen = len(@String)

	--Checking that the separator is present	
	set @RowNumber=1

	while @Break <> 1
	begin		
		set @SeparatorIndex = charindex(@Separator, @String, @StartIndex)
		
	
		if @SeparatorIndex = 0 
		begin --if the next separator is not found - use the end of the Csl string as the ending point			
			set @SeparatorIndex = len(@String)+1
			set @BackTrackIndex = @SeparatorIndex - 1
			set @Break = 1
		end
		else
		begin
			set @BackTrackIndex = @SeparatorIndex - 1
			while @BackTrackIndex > 0 AND substring(@String, @BackTrackIndex, 1) = '\'
			begin
				set @BackTrackIndex = @BackTrackIndex - 1
			end
		end
		
		
		
		--Checking if this was an escaped separator
		if (@SeparatorIndex - @BackTrackIndex - 1) % 2 = 0
		begin			
			if @Trim = 1
			begin
				set @Val = replace(ltrim(rtrim(substring(@String, @PrevIndex, @SeparatorIndex - @PrevIndex))), '\\', '\')
			end
			else
			begin
				set @Val = replace(substring(@String, @PrevIndex, @SeparatorIndex - @PrevIndex), '\\', '\')
			end	
			
			insert into @Table (RowNumber, RowValue) values (@RowNumber, @Val)
			set @RowNumber=@RowNumber+1
			
			set @PrevIndex = @SeparatorIndex + 1
		end
		
		set @StartIndex = @SeparatorIndex + 1
	end--end of loop

	RETURN 
END





Information Error Confirmation required