In this article I will explain with an example, how to split a String into Rows in SQL Server.
This article will illustrate how to create a function that will split a string separated (delimited) by comma, space or some other special characters into Table Rows in SQL Server.
 
 
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 SplitString function
The following script has to be executed. The SplitString 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
 
 
Split a String into Rows using SQL Query
The following SQL query split the string separated (delimited) by comma.
SELECT Item
FROM dbo.SplitString('Apple,Mango,Banana,Guava', ',')
 
Output
Split a String into Rows in SQL Server
 
 
Split a String into Rows using Stored Procedure
The following Stored Procedure needs to be created which will accept the 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
 
 
Executing the Stored Procedure
The Stored Procedure is executed which finally returns the FirstName and LastName records of Employees for which the Ids are passed using a string separated (delimited) by comma.
EXEC GetEmployees '1,3,5,9'
 
Output
Split a String into Rows in SQL Server
 
 
Downloads
Download Code