July 12, 2002 at 11:00 am
I need to be able to split a comma-delimited string in a table column and do a select in another table against these values. For instance a value like 6,7,8
i need to split into
select * from tblTest
where
tblTest.Id = '6' or
tblTest.Id = '7' or
tblTest.Id = '8'
thanks-
chris
</cm>
July 12, 2002 at 11:15 am
See my example in this thread
It will give you an idea of one optional way. Otherwise you may need to use a cursor.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
July 25, 2006 at 4:35 pm
Try this.
ALTER FUNCTION dbo.fnStringToTable(@CommaDelimList varchar(5000), @Delim char(1))
--*********************************
--Example;
--select *
--from dbo.fnStringToTable('one, two, three, four', ',')
--
--Author: Ed Hellyer
--Date: Tuesday July 26th 2006
--
--*********************************
RETURNS @List TABLE (Value varchar(100))
AS
BEGIN
declare @Start int
set @Start = 1
while @Start is not null
begin
insert into @List(Value) select LTrim(RTrim(SubString(@CommaDelimList, @Start, abs(@Start - IsNull(NullIf(CharIndex(@Delim, @CommaDelimList, @Start + 1), 0), Len(@CommaDelimList) + 1)))))
set @Start = NullIf(CharIndex(@Delim, @CommaDelimList, @Start + 1), 0) + 1
end
return
END
July 26, 2006 at 2:39 am
DECLARE @SQL VARCHAR(8000),
@Field VARCHAR(100)
SELECT @Field = MyField -- This is where you get the "6,7,8" string...
FROM MyTable
WHERE SomeID = 31
SELECT @SQL = 'SELECT * FROM tblTest WHERE ID IN (' + @Field + ')'
EXEC (@SQL)
N 56°04'39.16"
E 12°55'05.25"
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply