In this short article I will share with an example, a function to split a string in SQL Server 2005, 2008 and 2012 versions. The string containing words or letters separated (delimited) by comma will be split into Table values.
I will also explain how to use the Split function to split a string in a SQL Query or Stored Procedures in SQL Server 2005, 2008 and 2012 versions.
 
 
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
Split function in SQL Server Example: Function to Split Comma separated (Delimited) string in SQL Server 2005, 2008 and 2012
 
 
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
Split function in SQL Server Example: Function to Split Comma separated (Delimited) string in SQL Server 2005, 2008 and 2012
 
 
Downloads