Working with comma separated strings in t-SQL

Problem

When working with t-SQL you might run into a scenario where you want to return all the records that corresponds to the IDs found in a comma separated list, e.g. ‘123,456,789’. There are several ways to solve this problem and while browsing around I found an interesting solution.

Solution

In the example below I take advantage of the CHARINDEX and PATINDEX functions in t-SQL to search for specific patterns in the comma-separated string by concatenating the customer ID with commas and then match the concatenated string with the id string. The functions returns a number  greater than 0 if the sought string exists in the ID string which can be useful in my scenario where I got a comma separated list with customer IDs.

CHARINDEX works similar to the Substring or InStr functions that you can find in other programming languages. The function takes three parameters and the first is the string to search for, the second is the string to search and the third (which is optional) is the starting point where the search will begin in the string to search.

PATINDEX works similar to CHARINDEX, but allows the use of wildcards when performing the search. The function takes two parameters and the first is the string or pattern to search for and the second is the column or variable that will be searched. The method returns an integer bigger.

The example below also show that LIKE can be used to search the comma-separated string for specific IDs using a concatenated value along with wildcards.

USE [master]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE DATABASE [WeirdScience]
GO

USE [WeirdScience]
GO

CREATE TABLE [dbo].[Customers](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Customer] [nvarchar](50) NOT NULL
) ON [PRIMARY]
GO

INSERT Customers (Customer) VALUES (N'Dan Jansson')
INSERT Customers (Customer) VALUES (N'Lars Svensson')
INSERT Customers (Customer) VALUES (N'Lisa Karlsson')
INSERT Customers (Customer) VALUES (N'Kalle Johansson')
INSERT Customers (Customer) VALUES (N'Gullan Bengtsson')
GO

DECLARE @customerstring varchar(max);
SET @customerstring = '1,3,4,6'

-------------------------------
-- This example is based on the following article:
-- http://www.projectdmx.com/tsql/sqlarrays.aspx
-------------------------------

SELECT *
  FROM Customers
 WHERE CHARINDEX(
	',' + CAST(ID AS NVARCHAR(50)) + ',',
	',' + @customerstring + ','
	) > 0

SELECT *
  FROM Customers
 WHERE PATINDEX(
	'%,' + CAST(ID AS NVARCHAR(50)) + ',%',
	',' + @customerstring + ','
	) > 0

SELECT *
  FROM Customers
 WHERE ',' + @customerstring + ','
  LIKE '%,' + CAST(ID AS NVARCHAR(50)) + ',%'

USE master
GO

DROP DATABASE WeirdScience
GO

For more ideas on how to work with arrays in SQL Server you should take a look at the excellent article found here: http://www.projectdmx.com/tsql/sqlarrays.aspx

Advertisement