Splitting String
-
Hi All (SqL Server 2000)
Can some lend a hand with the following problem...
I have a stored proc which takes a comma seperated list of parent level IDs which is parsed out in the Where clause using a UDF. Only records whose foreign key - parent level ID which are in the passed list of IDs are returned. This works fine.
However, from the passed list of IDs, I also need to return an order number for records included as part of the select, starting from 0
Example...
Passed IDs '1,23,45,22,5,100,'
I need to return
person 1, parent 1, 0
person 2, parent 1, 0
person 3, parent 23, 1
person 4, parent 23, 1
person 78, parent 22, 3
person 101, parent 22, 3
person 67, parent 5, 4
person 108, parent 100, 5
The current UDF I use for the initial split is as follows...
CREATE FUNCTION dbo.udf_parse_string_into_integer_table
(@parseString varchar(8000)=null)
RETURNS @parsedstring TABLE (splitstring int)
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
Thanks
-
Add an identity column to your table function table. For example:
CREATE FUNCTION dbo.udf_parse_string_into_integer_table
(@parseString varchar(8000)=null)
RETURNS @parsedstring TABLE (ID int IDENTITY(0,1), splitstring int)
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
-
There's good article by Erland Sommarskog on this where he compares all the different methods for doing this
http://www.sommarskog.se/arrays-in-sql-2005.html#iterative
David
-
You could also search SSC for the word 'split' and find a few examples of how to use a Numbers table with a table valued 'split' function.
-
John, adding the identity column worked a treat - seems so obvious now
Thanks.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply