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