WiseOwl Training - Established 1992 Wise Owl Training

Established May 1992
30 years in business
Wise Owl Training
30 years in business
See 519 reviews for our classroom and online training
A table-valued function in SQL to split a comma-delimited list
If you're creating Reporting Services reports using multivalue parameters and stored procedures, you'll find this function to split a list of values into a single-table column useful.

Posted by Andy Brown on 11 June 2019

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

An SQL function to split a comma-delimited list of values

There is now a built-in function called String_Split within SQL to do this, so if you're using SQL Server version 2016 you may be able to disregard the rest of this page.

Suppose you have a string of values, separated by commas, and you want to turn them into a single-column table.  For example, you want to take an input like this:


And turn it into this:

Table of values

The single-column table of values you want to produce from the comma-delimited string of text given.


This blog gives an SQL table-valued function to do this, which you are welcome to copy and paste!

I've written this blog as a support to our manual chapter on using multivalue parameters in SSRS reports, although it's possible it may have more general use.

The SQL table-value function

Here's one way to achieve this:


@ids varchar(max)


-- function returns a single-column table of IdValue numbers


IdValue varchar(10)



-- get rid of any spaces (shouldn't be any)

SET @ids = REPLACE(@ids,' ', '')

-- if no ids passed, exit

IF len(@ids) = 0 RETURN

-- where to start looking ...

DECLARE @start int = 1

-- position of first comma found

DECLARE @end int = CHARINDEX(',', @ids)

-- if no comma, just return the one value

IF @end = 0


INSERT INTO @IdValues(IdValue) VALUES (@ids)



-- keep looking till no more commas

WHILE @end > 0


-- add this row

INSERT INTO @IdValues (IdValue) VALUES (

SUBSTRING(@ids, @start, @end - @start)


-- go on to next comma

SET @start = @end + 1

SET @end = CHARINDEX(',', @ids, @start)


-- add final row

INSERT INTO @IdValues (IdValue) VALUES (

SUBSTRING(@ids, @start, LEN(@ids) - @start + 1)




When you run this code, you should get a new table-valued function in your database:

New table-valued function

You may need to right-click on your list of table-valued functions to refresh it, to make your new function appear in the list.


Testing your new function

To check the new function works, create a new query in the same database and try running some checks:

-- try with more than 2 values

select * from dbo.fnSplitIds('44,222,28')

-- try with no values

select * from dbo.fnSplitIds('')

-- try with one value

select * from dbo.fnSplitIds('WiseOwl')

Here's what this should show:

Output from tests

The SQL commands should show 3, 0 and 1 rows respectively.


You can now call your function from any other SQL query!


This blog has 0 threads Add post