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