SQL Server 2008 Split Function Example to Split Comma Separated String into Table Rows

Description:

In this example we explain that how to split comma separated string into table values in SQL Server database or how to use split() function to split the comma separated string in SQL query.

Here we pass comma separated (delimiter) values as parameter to function in SQL Server. The comma separated values will be split into table rows and returns the result as a rows of table.

Here in this example we simply create one function that will take one parameter like as a string and return the table row by split the argument value based on delimiter specified in query and return table row. Below is the function for splitting the string into table rows in SQL Server:

Function:-

/****** Object:  UserDefinedFunction [dbo].[Split]    Script Date: 01/30/2016 13:59:37 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER FUNCTION [dbo].[Split](@String nvarchar(4000), @Delimiter char(1))

RETURNS @Results TABLE (Items nvarchar(4000),id int)

AS

BEGIN

DECLARE @cnt INT=1

DECLARE @INDEX INT

DECLARE @SLICE nvarchar(4000)

-- HAVE TO SET TO 1 SO IT DOESNT EQUAL Z

--     ERO FIRST TIME IN LOOP

SELECT @INDEX = 1

WHILE @INDEX !=0

BEGIN

-- GET THE INDEX OF THE FIRST OCCURENCE OF THE SPLIT CHARACTER

SELECT @INDEX = CHARINDEX(@Delimiter,@STRING)

-- NOW PUSH EVERYTHING TO THE LEFT OF IT INTO THE SLICE VARIABLE

IF @INDEX !=0

SELECT @SLICE = LEFT(@STRING,@INDEX - 1)

ELSE

SELECT @SLICE = @STRING

-- PUT THE ITEM INTO THE RESULTS SET

INSERT INTO @Results(Items,id) VALUES(@SLICE,@cnt)

set @cnt=@cnt+1

-- CHOP THE ITEM REMOVED OFF THE MAIN STRING

SELECT @STRING = RIGHT(@STRING,LEN(@STRING) - @INDEX)

-- BREAK OUT IF WE ARE DONE

IF LEN(@STRING) = 0 BREAK

END

RETURN

END

After create function in your database you can simply use this function by below query like:

SELECT Items FROM dbo.Split('Cricket,Tennis,Football', ',')