Tuesday, November 14, 2006

SQL Server Split Function

Ever needed to pass a comma deliminated string into SQL Server as a parameter and have it split the string so you could then use each value individually? I have, and this function has gotten a lot of use. Unfortunately, I didn't write it, and I cannot exactly remember where on the net I found it, but I think it came from this post:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648

here is the function I use:

ALTER FUNCTION dbo.func_Split
(
@List nvarchar(2000),
@SplitOn nvarchar(5)
)
RETURNS @RtnValue table
(

Id int identity(1,1),
Value nvarchar(100)
)
AS
BEGIN
While (Charindex(@SplitOn,@List)>0)
Begin
Insert Into @RtnValue (value)
Select Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1)))
Set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))
End

Insert Into @RtnValue (Value)
Select Value = ltrim(rtrim(@List))

Return
END

No comments: