How to split a string delimited by comma in sql server

In this short article i will explain about How to split a string in sql server 2008,2012 using a function.I recently worked on this requirement while developing a web service and i want to share it to you.Here i used a sql server function that takes input as string and returns a table columns as output.And i will explain how to use this custom function in another stored procedure or sql query to get desired output.

Below is the custom split function to split comma separated string into table columns.
CREATE FUNCTION dbo.SplitString(@String nvarchar(max), @Delimiter char(1))
RETURNS @Results TABLE (Items nvarchar(max))
AS
BEGIN
DECLARE @INDEX INT
DECLARE @SLICE nvarchar(4000)
SELECT @INDEX = 1
WHILE @INDEX !=0
BEGIN
SELECT @INDEX = CHARINDEX(@Delimiter,@STRING)
IF @INDEX !=0
SELECT @SLICE = LEFT(@STRING,@INDEX - 1)
ELSE
SELECT @SLICE = @STRING
INSERT INTO @Results(Items) VALUES(@SLICE)
SELECT @STRING = RIGHT(@STRING,LEN(@STRING) - @INDEX)
IF LEN(@STRING) = 0 BREAK
END
RETURN
END
After execution of this script in database we can use it any sql query or stored procedure where you want to split string to table result.This function accepts two parameters and returns table output.


@String: This parameter accepts string to split.
@Delimiter: This accepts character that used to separate delimited string.

Using Splitstring function in sql query:

Run the below sql query to see the example of using split string function in query.
select items from SplitString('ravi,peter,allabakash',',')
once you execute this query we get output like below.

I hope this article helps you.We can use this function in stored procedure as well.In later articles i will share more knowledge on sql and as well as other technologies.
How to split a string delimited by comma in sql server How to split a string delimited by comma in sql server Reviewed by raviteja swayampu on 21:07:00 Rating: 5

No comments: