BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
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:
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!