Friday, December 16, 2005

A little while back I had cause to take in a string of delimited values and return a table.  This morning I had a request for the same code, so I figured that I'd put it out here for all to see.  Some of you may already have your own version of this code, but here's mine.

The function below takes in a single string (i.e. 'Fred, Barney, Wilma, Betty') and a delimiter character as parameters and returns the values as a table which can then be sorted, searched, etc.  It will return 2 columns (ID which is an identity and DATA which is your list items). 

You would use it like this:
    SELECT * FROM fn_SplitString('Fred, Barney, Wilma, Betty', ',' )


Here's the code for the function:

CREATE FUNCTION fn_SplitString (@Data varchar(8000), 
@Delimiter varchar(10))
RETURNS @MyTable TABLE     (     Id int identity(1,1),     Data nvarchar(100)     )
BEGIN Declare @Count int Set @Count = 1
While (Charindex(@Delimiter, @Data) >0) Begin Insert Into @MyTable (Data) Select Data = ltrim(rtrim(Substring
Set @Data = Substring(@Data,Charindex
(@Delimiter,@Data)+1,len(@Data)) Set @Count = @Count + 1 End
Insert Into @MyTable (data) Select Data = ltrim(rtrim(@Data))
Chris Antoniak  DBA/Developer


Recent Entries

Blogs we read

Page 1 Of 1 (1 items)