SQL function to split the comma separated string and convert to a table of separate value

Puneet20884
Posted by Puneet20884 under Sql Server category on | Views : 8313
Hi,
Sometimes we need to split a comma separated string / text in SQL and to return each of the item as a separate record in a table.
e.g.
we have 'nitin,sumit,ami,manpreet,puneet,lucky,nishant' etc.
and i want an output as a table with

nitin
sumit
ami
manpreet
puneet
lucky
nishant

the following function solves this requirement and will also give the Position corresponding to each value, the output will be as

Position value
1 nitin
2 sumit
3 ami
4 manpreet
5 puneet
6 lucky
7 nishant
here the separater is not necessarily to be comma, you can use any separater e.g. dot,hash etc.


CREATE FUNCTION dbo.as_fn_Split(@text varchar(8000), @delimiter varchar(20) = ' ')
RETURNS @Strings TABLE
(
position int IDENTITY PRIMARY KEY,
value varchar(8000)
)
AS
BEGIN
DECLARE @index int
SET @index = -1
WHILE (LEN(@text) > 0)
BEGIN
SET @index = CHARINDEX(@delimiter , @text)
IF (@index = 0) AND (LEN(@text) > 0)
BEGIN
INSERT INTO @Strings VALUES (@text)
BREAK
END
IF (@index > 1)
BEGIN
INSERT INTO @Strings VALUES (LEFT(@text, @index - 1))
SET @text = RIGHT(@text, (LEN(@text) - @index))
END
ELSE
SET @text = RIGHT(@text, (LEN(@text) - @index))
END
RETURN
END


Now, Coming to the points, how it works.
In SQL 2005 onwards, there is a data type "table", meaning one can create a table variable.
So the function's return value taken is of Table type.
The function when you look a bit deeply, is self explanatory.
Here we loop on the length of the text passed (comma separated text), till the length is more than 0 (Zero),
and we table one by the first comma separated value from this text and insert that to a table variable and remove from the text that particular value.
Hence in the end we reach to the text with length 0 and all the items in the table variable and the table is returned in the end.

An example, how to use it :
Select * from dbo.as_fn_Split('nitin,sumit,ami,manpreet,puneet,lucky,nishant',',')
it will give the output mentioned above.

If there is any question, feel free to contact,
Thanks,
Puneet

Comments or Responses

Posted by: Webmaster on: 2/17/2010 Level:HonoraryPlatinum | Status: [Administrator]
Hi Puneet,

Please explain your code in little details and also show how to use it. Please keep your codes in this post inside
 
block to make it easily readable.

Thank you
Posted by: Puneet20884 on: 2/18/2010 Level:Bronze | Status: [Member]
Hi Webmaster It's done now,
However formatting, the editor is not taking.
Posted by: Webmaster on: 2/18/2010 Level:HonoraryPlatinum | Status: [Administrator]
We have done this form you, please go to edit mode and see how its done. Basically code should come under code block, you will need to select the text and click on Code toolbar.

Thank you and keep it up!

Posted by: Puneet20884 on: 2/18/2010 Level:Bronze | Status: [Member]
Thank you webmaster !! :)
Posted by: Jayakumars on: 3/20/2013 Level:Bronze | Status: [Member] [MVP] | Points: 10
hi

how to come this column wise puneet?

Login to post response