Parsing a delimited string

Most often I’ve seen posts where the requirement is to parse a delimited string and convert it to table of values. Below is a table valued function which I use to parse a delimited string and convert it to table of values

CREATE FUNCTION ParseValues

(@String varchar(8000), @Delimiter varchar(10) )
RETURNS @RESULTS TABLE (ID int identity(1,1), Val varchar(8000))
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

Example usage

select * from dbo.ParseValues(‘This|~~|is|~~|a|~~|delimited|~~|string|~~|value’,’|~~|’)

Result


Published by dataplatformcentral

2 thoughts on “Parsing a delimited string”

  1. Would be a great help if you could tel me how to do this.

    below is a sample of my data.

    ab_Data,25882,1533,Thu May 10 21:32:23 2012,6,2,0,sgtin:0697662.007377.42200000978|sgtin:0697662.009417.42200001281|,0000000000000001000000000000001F00015F90000000640000000000000BB8000000190000000A0000000100000001000007D000001B580000000A00000004000009C40000006400000007000000C80000001E00000000000000640000000F0000012C000006400000001E0000000100000032000001F4000000320000006400000064000000010000000A002DC6C00000AFC80000000100000CE1000000000000000000000002000003250000000200007562000014F7000000000000000100000000000000020000000800000000

    i have more than hundred different flat files with such rows.

    i want it to be put in different columns in SQL server DB table, Values seperated by Comma and by special Character '|', it doesnt matter if the last part (after the last '|' can be deleted and is not necessary).

    thanks in advance.

Comments are closed.