Here is a SQL Server 2000 user defined function which takes a comma
delimited string of numbers as input, and returns a table variable
containing one row for each element of that string.

Perhaps you can adapt it for your use.

-------
CREATE FUNCTION Inlist (@list varchar(8000))
 RETURNS @tbl TABLE (val int not null)  AS
 BEGIN
 	Declare @index int,
 		@pos int,
 		@str varchar(8000),
 		@num int
 	Set @pos = 1
 	Set @index = 1
 	While @index > 0
 	Begin
 		set @index = charindex(',', @list, @pos)
 		if @index > 0
 			Set @str = substring(@list, @pos, @index - @pos)
 		Else
 			Set @str = substring(@list, @pos, Len(@list))
 		     Set @str = ltrim(rtrim(@str))
 		Set @num = cast(@str as integer)
 		Insert @tbl (val) values (@num)
 		Set @pos = @index + 1
 	End
 	Return
 END

--
Jeff Mason              Custom Apps, Inc.
Jeff@c...

-----Original Message-----
From: Gillian Leech [mailto:gillianleech@h...]
Sent: Thursday, February 27, 2003 10:38 AM
To: sql language
Subject: [sql_language] substrings


Dear All,

Can anyone tell me how to do the following:

I have a table and in it a FieldName column and a SourceID column with
some info for each, which looks like this:

FieldName       SourceID
SomeTextHere    1,2,3,4,5


Is it possible to write some SQL so when returning the values of the
table, i can split the numbers separated by the commas so the results are
shown like this

FieldName       SourceID
SomeTextHere    1
SomeTextHere    2
SomeTextHere    3
SomeTextHere    4
SomeTextHere    5

Any help would be great

Cheers

Gill