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

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:

44,222,282

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:

CREATE FUNCTION fnSplitIds(

@ids varchar(max)

)

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

RETURNS @IdValues TABLE(

IdValue varchar(10)

)

BEGIN

-- 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

BEGIN

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

RETURN

END

-- keep looking till no more commas

WHILE @end > 0

BEGIN

-- 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)

END

-- add final row

INSERT INTO @IdValues (IdValue) VALUES (

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

)

RETURN

END

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