Technical Article

Parse a delimited string, return n-th value

,

This UDF will parse a string delimited by the character you specify and return the value in the n-th position you requested.

Example:
select dbo.fn_parse('this string', ' ', 1) --> 'this'
select dbo.fn_parse('this string', ' ', 2) --> 'string'
select dbo.fn_parse('this string', ' ', 3) --> NULL
select dbo.fn_parse('this  string', ' ', 2) --> ''


CREATE  FUNCTION fn_parse (@str varchar(255), @delim char(1), @token tinyint)
RETURNS varchar(255)
AS
BEGIN
DECLARE @start tinyint
,@end tinyint
,@loopcnt tinyint

set @end = 0
set @loopcnt = 0
set @delim = substring(@delim, 1, 1)

--loop to specific token
while (@loopcnt < @token) begin
set @start = @end + 1
set @loopcnt = @loopcnt + 1
set @end = charindex(@delim, @str+@delim, @start)
if @end = 0 break
end

if @end = 0
set @str = null
else
set @str = substring(@str, @start, @end-@start)

RETURN @str

END

Rate

1 (1)

You rated this post out of 5. Change rating

Share

Share

Rate

1 (1)

You rated this post out of 5. Change rating