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

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

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

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