User defined function to parse delimited strings

,

This UDF parses a comma delimited string and returns a table with the parse strings as rows.
Usage:  select * from fn+PareseString('12,13,14,67')
Will return a table with the following rows
12
13
14
67

Can be modified to have an additional parameter for the type of delimiter.

--Author : Madhusudan  May 02,2002
--Parses a comma delimited string and returns a table with ---the parsed strings as rows
CREATE FUNCTION dbo.fn_ParseString 
(@parseString varchar(255)=null)
RETURNS @parsedstring TABLE (splitstring varchar(255))
as

begin
declare @pos int
declare @splitstring varchar(255)
declare @strlen int
select @strlen = len(ltrim(@parsestring))
if @strlen<> 0   
BEGIN
while @strlen > 0
  begin
    select @pos = charindex(',',@parsestring) 
    if @pos = 0
       begin
       insert into @parsedstring values ( @parsestring)
       break
       end
    select @splitstring = substring(@parsestring,1,@pos-1)
    insert into  @parsedstring  values( @splitstring)
    
    select @strlen= @strlen - @pos
    select @parsestring = substring(@parsestring,@pos+1,@strlen)



  
  end
end
RETURN 
END

Rate

Share

Share

Rate