Last Updated: February 25, 2016
·
1.143K
· lyndsysimon

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;