This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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

In this blog

The SQL table-value function
Testing your new function

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:

44,222,282

And turn it into this:

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:

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:

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

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

Some other pages relevant to the above blog include:

This blog has 0 threads

Add a new post

BLOGS BY TOPIC

BLOGS BY AUTHOR

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

The SQL table-value function

Testing your new function

Head office

London

Manchester