If you ever had the need to parse a delimited string in SQL Server, then you know that there is no in-built function that does that. The function I’m about to write about is particullary useful if you are passing a parameter that contains multiple string values (eg. passing a parameter from the client side to a report instance in Reporting services) and you want to use it as a filter for “IN” expression in SQL.
In the following example, we wil populate a simple table variable with a short list of people with their first name, last name and birth city. After that, we will write a query that will return only people that have a birth city that matches one of the cities we've passed in a parameter.
First, let’s create and populate a simple table with three columns:
DECLARE @PeopleTable TABLE
(
FirstName varchar(20),
LastName varchar(20),
BirthCity varchar(20)
)
INSERT INTO @PeopleTable
SELECT 'Lisa', 'Martin', 'Chicago'
UNION
SELECT 'Brad', 'Brown', 'Chicago'
UNION
SELECT 'Adrian', 'Wilson', 'Denver'
UNION
SELECT 'Adam', 'Clark', 'Denver'
UNION
SELECT 'Tim', 'Taylor', 'New York'
UNION
SELECT 'Steven', 'Campbell', 'New York'
UNION
SELECT 'Molly', 'Jones', 'Washington'
UNION
SELECT 'Stacy', 'Santiago', 'Washington'
Now, take a look at the data we inserted into the table:
SELECT * FROM @PeopleTable
| FirstName |
LastName |
BirthCity |
| Adam |
Clark |
Denver |
| Adrian |
Wilson |
Denver |
| Brad |
Brown |
Chicago |
| Lisa |
Martin |
Chicago |
| Molly |
Jones |
Washington |
| Stacy |
Santiago |
Washington |
| Steven |
Campbell |
New York |
| Tim |
Taylor |
New York |
For this example, let’s filter the data in the PeopleTable by people that were born in Chicago or Denver. Normally, you would write something like this:
SELECT *
FROM @PeopleTable
WHERE BirthCity IN ( 'Chicago','Denver')
ORDER BY FirstName
| FirstName |
LastName |
BirthCity |
|
| Adam |
Clark |
Denver |
| Adrian |
Wilson |
Denver |
| Brad |
Brown |
Chicago |
| Lisa |
Martin |
Chicago |
But we want to pass the list of cities as a parameter:
DECLARE @DelimitedStringParameter varchar(60)
SET @DelimitedStringParameter = 'Chicago,Denver'
If we run the following query, it would return zero rows.
SELECT *
FROM @PeopleTable
WHERE BirthCity IN (@DelimitedStringParameter)
The solution to this problem is the following function:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[ParseDelimitedString]
(
@String nvarchar(max),
@Delimiter nvarchar(1)
)
RETURNS
@OutputTable TABLE
(
ParsedString nvarchar (50)
)
AS
BEGIN
DECLARE @DelimiterIndex int
SET @DelimiterIndex = -1
WHILE (LEN(@String) > 0)
BEGIN
SET @DelimiterIndex = CHARINDEX(@Delimiter, @String)
IF (@DelimiterIndex = 0) AND (LEN(@String) > 0)
BEGIN
INSERT INTO @OutputTable VALUES (LTRIM(RTRIM(@String)))
BREAK
END
IF (@DelimiterIndex > 1)
BEGIN
INSERT INTO @OutputTable VALUES (LTRIM(RTRIM(LEFT(@String, @DelimiterIndex - 1))))
SET @String = RIGHT(@String, (LEN(@String) - @DelimiterIndex))
END
ELSE SET @String = RIGHT(@String, (LEN(@String) - @DelimiterIndex))
END
RETURN
END
The usage of
ParseDelimitedString function:
SELECT ParsedString AS City
FROM dbo.ParseDelimitedString (@DelimitedStringParameter , ',')
Now, let’s filter the data from PeopleTable:
SELECT *
FROM @PeopleTable
WHERE
BirthCity IN
(
SELECT ParsedString
FROM dbo.ParseDelimitedString (@DelimitedStringParameter , ',')
)
ORDER BY
FirstName
| FirstName |
LastName |
BirthCity |
| Adam |
Clark |
Denver |
| Adrian |
Wilson |
Denver |
| Brad |
Brown |
Chicago |
| Lisa |
Martin |
Chicago |
Alternatively, we can filter the data using an inner join:
SELECT p.*
FROM
@PeopleTable p
INNER JOIN
(
SELECT ParsedString
FROM dbo.ParseDelimitedString (@DelimitedStringParameter , ',')
) y ON p.BirthCity = y.ParsedString
ORDER BY
FirstName
Please keep in mind that if you are gong to be using this function in a query that fetches a large chunk of data, it is advisable to store the parsed data into a temporary table that will be used in a query, so
ParseDelimitedString function gets executed only once.
Let’s create the table variable that will be used to store the values returned by
ParseDelimitedString function:
DECLARE @ParsedStringTable TABLE
(
BirthCity varchar(20)
)
The usage of ParseDelimitedString function:
INSERT INTO @ParsedStringTable
SELECT ParsedString FROM dbo.ParseDelimitedString (@DelimitedStringParameter , ',')
Our queries now look like this:
SELECT *
FROM @PeopleTable
WHERE BirthCity IN ( SELECT BirthCity FROM @ParsedStringTable )
ORDER BY
FirstName
And this:
SELECT p.*
FROM
@PeopleTable p
INNER JOIN ( SELECT BirthCity FROM @ParsedStringTable ) y ON p.BirthCity = y.BirthCity
ORDER BY
FirstName