In this short article I will share with an example, how to split and convert a comma separated / delimited string to a table using Split function 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.

Install Microsoft Northwind and Pubs Sample databases in SQL Server Management Studio

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

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