Here is a MS SQL Server (T-SQL) user defined function to split up a delmited string, and return the index value you care about.
Example:
select dbo.GetValueOfSplitStringByIndex(',','abc,def,hhhhhhh,l',1)
returns:
'def'
CREATE FUNCTION dbo.GetValueOfSplitStringByIndex (@sep char(1), @s varchar(1540), @index int)
RETURNS varchar(1540)
AS
-- Takes a @sep separated string, turns it into a list internally and returns the value from that
-- list found at the @index location. Null is return if that @index does not exist.
-- Sample usage (testing):
-- select dbo.GetValueOfSplitStringByIndex('-','200-2505-0000-01-149-101-10',1)
-- select dbo.GetValueOfSplitStringByIndex(',','abc,def,hhhhhhh,l',0)
-- select dbo.GetValueOfSplitStringByIndex(',','abc,def,hhhhhhh,l',1)
-- select dbo.GetValueOfSplitStringByIndex(',','abc,def,hhhhhhh,l',2)
-- select dbo.GetValueOfSplitStringByIndex(',','abc,def,hhhhhhh,l',3)
-- select dbo.GetValueOfSplitStringByIndex(',','abc,def,hhhhhhh,l',4)
-- select dbo.GetValueOfSplitStringByIndex(',','abc,def,hhhhhhh,l,',0)
-- select dbo.GetValueOfSplitStringByIndex(',','abc',0)
Begin
declare @pos int
declare @piece varchar(500)
declare @curr_index int
set @curr_index = 0
-- Need to tack a delimiter onto the end of the input string if one doesn't exist
if right(rtrim(@s),1) <> @sep
set @s = rtrim(@s) + @sep
set @pos = patindex('%' + @sep + '%' , @s)
while @pos <> 0
begin
set @piece = left(@s, @pos - 1)
-- You have a piece of data, so insert it, print it, do whatever you want to with it.
if @curr_index=@index
return cast(@piece as varchar(1540))
set @s = stuff(@s, 1, @pos, '')
set @pos = patindex('%' + @sep + '%' , @s)
set @curr_index = @curr_index + 1
end
return Null
end