Comma separated / delimited string to a table in SQL Server

Recently, I came across a piece of TSQL code that would take a comma separated string as an input and parse it to return a single column table from it.
CREATE FUNCTION [dbo].[UDF_string_to_table]

(
@string VARCHAR(8000),
@delimiter CHAR(1)
)
RETURNS @output TABLE(CommaSeparatedString VARCHAR(256))

BEGIN

DECLARE @start INT, @end INT
SELECT @start = 1, @end = CHARINDEX(@delimiter, @string)

WHILE @start < LEN(@string) + 1 BEGIN
IF @end = 0
SET @end = LEN(@string) + 1

INSERT INTO @output (CommaSeparatedString) VALUES(SUBSTRING(@string, @start, @end – @start))

SET @start = @end + 1
SET @end = CHARINDEX(@delimiter, @string, @start)
END

RETURN
END

Output:

select * from UDF_string_to_table(‘1,2,3′,’,’)

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s