String Parsing

,

User Defined function to parse a string with known delimiters and return back the substing in the position.
Takes string,delimiter and position as parameters. Can be used directly in TSQL code.

CREATE function ufn_parsestring (
@string varchar(255),
@delimiter char(1),
@value int)
returns varchar(255)
as
begin
declare @index int
declare @charpos int
declare @startpos int

set @index = 0
set @charpos = 1
set @startpos = 0
set @string = @string + @delimiter

while @index < @value - 1 and @charpos <= len(@string)
begin
set @charpos = @charpos + 1
if substring(@string,@charpos,1) = @delimiter
begin
  set @index = @index + 1
end
end

if @charpos > len(@string)
begin
  return NULL
end
 
if @value = 1
 set @startpos = -1
else
 set @startpos = @charpos + 1

 set @charpos = @startpos
 while substring(@string,@charpos,1) <> @delimiter and @charpos <= len(@string)
 begin
   set @charpos = @charpos + 1
 end
if @charpos > len(@string)
begin
return NULL
end
 return (substring(@string,@startpos,(@charpos) - @startpos))

end

Rate

Share

Share

Rate