In this short article with an example, how to use Comma Separated (Delimited) string values with IN and WHERE clause in SQL Server.
This article is applicable to all SQL Server versions i.e. 2005, 2008, 2012 and 2014.
Database
For this article I am making use of the Microsoft’s Northwind Database. Download and install instructions are provided in the link below.
The Split string function
The following script has to be executed in your database. The Split string function is a Table-Valued function i.e. it returns a Table as output and accepts two parameters namely
1. @Input – The string to be split.
2. @Character – The character that is used to separate the words or letters.
CREATE FUNCTION SplitString
(
@Input NVARCHAR(MAX),
@Character CHAR(1)
)
RETURNS @Output TABLE (
Item NVARCHAR(1000)
)
AS
BEGIN
DECLARE @StartIndex INT, @EndIndex INT
SET @StartIndex = 1
IF SUBSTRING(@Input, LEN(@Input) - 1, LEN(@Input)) <> @Character
BEGIN
SET @Input = @Input + @Character
END
WHILE CHARINDEX(@Character, @Input) > 0
BEGIN
SET @EndIndex = CHARINDEX(@Character, @Input)
INSERT INTO @Output(Item)
SELECT SUBSTRING(@Input, @StartIndex, @EndIndex - 1)
SET @Input = SUBSTRING(@Input, @EndIndex + 1, LEN(@Input))
END
RETURN
END
GO
Using the Split String function in SQL Query
SELECT Item
FROM dbo.SplitString('Apple,Mango,Banana,Guava', ',')
Output
![Use Comma Separated (Delimited) string values with IN and WHERE clause in SQL Server]()
Using the Split String function in a Stored Procedure
The following stored procedure gets the records of Employees for which the Ids are passed using a string separated (delimited) by comma.
CREATE PROCEDURE GetEmployees
@EmployeeIds VARCHAR(100)
AS
BEGIN
SELECT FirstName, LastName
FROM Employees
WHERE EmployeeId IN(
SELECT CAST(Item AS INTEGER)
FROM dbo.SplitString(@EmployeeIds, ',')
)
END
The stored procedure is executed as follows
EXEC GetEmployees'1,3,5,9'
Output
![Use Comma Separated (Delimited) string values with IN and WHERE clause in SQL Server]()
Downloads
