This script is a scalar function, that will allow you to Split and return an index value, it even handles if there is no value split to return:
CREATE function [dbo].[SplitString](
@String nvarchar (max),
@Delimiter nvarchar (10),
@position int
)
RETURNS varchar(max)
AS
begin
declare @NextString nvarchar(max)
declare @Pos int
declare @NextPos int
declare @CommaCheck nvarchar(1)
declare @count int
declare @ValueTable table (ID int, Value varchar(max))
--Initialize
set @NextString = ''
set @CommaCheck = right(@String,1)
set @count = 0
--Check for trailing Comma, if not exists, INSERT
--if (@CommaCheck <> @Delimiter )
set @String = @String + @Delimiter
--Get position of first Comma
set @Pos = charindex(@Delimiter,@String)
set @NextPos = 1
--Loop while there is still a comma in the String of levels
while (@pos <> 0)
begin
set @NextString = substring(@String,1,@Pos - 1)
insert into @ValueTable ( [ID], [Value]) Values (@count, @NextString)
set @count = @count + 1
set @String = substring(@String,@pos +1,len(@String))
set @NextPos = @Pos
set @pos = charindex(@Delimiter,@String)
end
declare @val varchar(255)
select @val = value from @ValueTable where ID = @position
return @val
end
