Arthur Fuller
artful at rogers.com
Mon Sep 1 12:59:40 CDT 2003
I'm trying to write a function that parses a string and returns a table of its values. So far I'm close but need some help on the final step. This preliminary codes in QA: declare @string varchar(50) set @string = '100, 200, 300, 400' declare @pos int while @string > '' begin set @pos = charindex(',', @string) if @pos > 0 begin select substring( @string, 1, @pos - 1) select @string = LTrim(substring( @string, @pos + 1, LEN(@string)- at pos+1)) select @string end else set @string = '' end This works fine and runs pretty quickly. Next, I tried to turn it into a table-valued function, like so: CREATE FUNCTION fn_StringToTable (@String varchar(50)) RETURNS @Values TABLE (ID int primary key) AS begin -- declare @string varchar(50) -- set @string = '100, 200, 300, 400' declare @pos int declare @value int while @string > '' -- begin set @pos = charindex(',', @string) if @pos > 0 begin set @value = substring( @string, 1, @pos - 1) select @string = LTrim(substring( @string, @pos + 1, LEN(@string)- at pos+1)) --select @string insert @Values SELECT @value end else set @string = '' RETURN end When I run this version, i.e. "select * from fn_StringToTable( '100, 200, 333, 444, 555')", it seems to go into space. Doesn't crash or anything, but I keep cancelling because it's taking so long. What am I doing wrong? TIA, Arthur --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.510 / Virus Database: 307 - Release Date: 8/14/2003