[dba-SQLServer]Close but no cigar

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



More information about the dba-SQLServer mailing list