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;