Split String from SQL Database

Sometimes you have to store information in one field.

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