Parsing a delimited string in SQL

 Most of the time we required the delimited strings to be added in database. today i come up with some simple solution. which will read the string and specified delimited character and base on that it will divide the string and split it across.

CREATE FUNCTION ParseValues

(@String varchar(8000), @Delimiter varchar(10) )

RETURNS @RESULTS TABLE (ID int identity(1,1), Val varchar(50))

AS

BEGIN

DECLARE @Value varchar(100)

WHILE @String is not null

BEGIN

SELECT @Value=CASE WHEN PATINDEX('%'+@Delimiter+'%',@String) >0 THEN LEFT(@String,PATINDEX('%'+@Delimiter+'%',@String)-1) ELSE @String END, @String=CASE WHEN PATINDEX('%'+@Delimiter+'%',@String) >0 THEN SUBSTRING(@String,PATINDEX('%'+@Delimiter+'%',@String)+LEN(@Delimiter),LEN(@String)) ELSE NULL END

INSERT INTO @RESULTS (Val)

SELECT @Value

END

RETURN

END

You can call this function in your query and it will return the result.(as show in img)

select * from dbo.ParseValues('This;is;a;delimited;string;value',';')

and here is your result..

   
 Hope this will helps you, Please put your comments or dought to help others.