Wednesday, December 26, 2012

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.