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