Split string separated by comma into rows in SQL

Introduction

The split function is a user defined function used to split string value separated by delimiter.

The string may contain alphanumeric value.

SQL Split Function

CREATE FUNCTION [dbo].[fnSplit]
(
    @sInputList VARCHAR(8000),         -- List of delimited items
    @sDelimiter VARCHAR(8000) = ','    -- delimiter that separates items
)

RETURNS @List TABLE (colData VARCHAR(8000))

BEGIN    
    DECLARE @sItem VARCHAR(8000)
    WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
        BEGIN
            SELECT @sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))), @sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))
    
            IF LEN(@sItem) > 0
                INSERT INTO @List SELECT @sItem
        END
        IF LEN(@sInputList) > 0
            INSERT INTO @List SELECT @sInputList -- Put the last item in
    RETURN
END

How to use split function

SELECT colData FROM dbo.fnSplit('1,22,333,444,,5555,666', ',')

Output

In the following example, I will explain you how to split a string value separated by a delimiter.

Let’s start with a database table.

Create a database table as follows:

Database table: tbl_student

Add some data in the table

Create a stored procedure

SELECT colData FROM dbo.fnSplit('1,22,333,444,,5555,666', ',')
CREATE PROCEDURE getStudentDetails
         @studentId VARCHAR(10)
AS
BEGIN
    SELECT first_name, last_name FROM tbl_student WHERE student_id IN
    (SELECT CAST(colData AS INTEGER) FROM dbo.fnSplit(@studentId, ','))
END

Retrieve student details using stored procedure

EXEC getStudentDetails '1,3,4'

Output