Last Updated: February 25, 2016

Parsing delimited/serialized fields in SQL Server

I recently had to deal with a serialized field in SQL Server. My first through was to simply use a regular expression to pull the second-to-last value from the field, but alas - SQL Server doesn't support sorcery.

Indeed, it seems that that string manipulation ability of SQL Server is sorely lacking in general.

My moaning about Microsoft's lack of foresight aside, here is the function I came up with to solve the problem:

create function [dbo].[fnGetDelimitedValue] (
 @string_to_search varchar(500),
 @delimiter varchar(500) = ',',
 @position int = 1
)
returns varchar(500)
begin

 declare @index int = 0;

 while @index < @position -1
 begin
 set @string_to_search = substring(@string_to_search, charindex(@delimiter, @string_to_search)+1, LEN(@string_to_search));
 set @index = @index + 1;
end 

set @string_to_search = substring(@string_to_search, 1, charindex(@delimiter, @string_to_search)-1)

return @string_to_search

end

And here's a simple test case I put together to show the rest of my team how the function could be used:

create table #temp (
 csv_field varchar(100)
);

insert into
 #temp
values
 ('a,b,c,d,e,f,g,h,i'),
 ('one,two,three,four,five,six,seven,eight,nine'),
 ('1,2,3,4,5,6,7,8,9'),
 ('alpha,bravo,charlie,delta,echo,
 foxtrot,golf,hotel,indigo');

select
 dbo.fnGetDelimitedValue(csv_field, ',', 1) first_value
 , dbo.fnGetDelimitedValue(csv_field,',',5) fifth_value
 , csv_field
from
 #temp;

drop table #temp;

#sql

#sql-server