Wednesday, March 21, 2007

SQL: Split function that parses comma delimated string

This is a function that I use frequently (also some of my friends). It is a function in Sql server, that parses a comma-deliminated string and returns a table. By returning a table, you can use this in joins. So, here is the function:

create function fn_Split(
@String
nvarchar (4000),
@Delimiter
nvarchar (10)
)
returns @ValueTable
table ([Value] nvarchar(4000))
begin
declare
@NextString nvarchar(4000)
declare @Pos int
declare
@NextPos int
declare
@CommaCheck nvarchar(1)

--Initialize
set @NextString = ''
set @CommaCheck = right(@String,1)

--Check for trailing Comma, if not exists, INSERT
if (@CommaCheck <> @Delimiter )
set @String = @String + @Delimiter

--Get position of first Comma
set @Pos = charindex(@Delimiter,@String)
set @NextPos = 1

--Loop while there is still a comma in the String of levels
while (@pos <> 0)
begin
set
@NextString = substring(@String,1,@Pos - 1)
insert into @ValueTable ( [Value]) Values (@NextString)
set @String = substring(@String,@pos +1,len(@String))
set @NextPos = @Pos
set @pos = charindex(@Delimiter,@String)
end

return
end
go


How do we use it? Like that:
SELECT * FROM ServicesTable inner join dbo.fn_Split(@ServiceFilter, ',') tbl on tbl.[Value] = ServicesTable.Service)

* Here, the @ServiceFilter is something like "abc,xyz,123".

2 comments:

Anonymous said...

Hi, im using this function
It works great when the function return numbers
But when returning strings it gives me the error: Cannot resolve collation conflict for equal to operation.

Query:
select * from Table1 T inner join fnSplitString ('a,b,c', ',') V on T.EMail = V.value

EMail field on Table1 is varchar(50) and hasdatabase_default as collation
Do you have any suggestions??

Thanks in advance

Anonymous said...

Rather quite good topic