[dba-Tech] Finding data in a list

So I created a little function to take a "list" of items and convert
it to a table. (see below).

What I want to do is query a table that has any one of these items in
"it's" list such as


Field1, Field2, Field3, Field4

SN    Option,  Date,  (Group1,Group2, Group3, Group4)


SOoooo... The data I wish to collect is how many times does my list of
IDs show up on the stored data


for example the SELECT * FROM udfListToTable('Group1,Group4'; ',') I'd
like to somehow join this to the primary table that has SN, Option,
DATE and the GroupListField

so if one row has the field GroupListField as (Group1,Group3,Group4)
it would show up, and so would the row that has (Group3,Group4) and
the row (Group1,Group2)

see what I mean? (OR perhaps I ought to store the individual groups
into their own rows on a sub row... I can go back and chage it as it's
still development.


btw, your thoughts are always welcomed...



CREATE FUNCTION udfListToTable (@HList VarChar(1000), @Delimiter CHAR(1))
RETURNS @ListTable TABLE (Field1 VARCHAR(6))
AS
BEGIN
	--By: Francisco Tapia
	--Date: 2/1/2005
	--Purpose: To convert a Comma delimited text to a Temp Variable table
To help avoid dynamic sql
	--  	   Instead you can join the temp table or use it in your where
clause if a field is IN the subquery
	DECLARE @SOURCEtext as VarChar(6)

	IF RIGHT(RTRIM(@HLIST),1) <>@Delimiter
		SET @HList = @HList + @Delimiter
	
	WHILE CHARINDEX(@Delimiter, @HList) > 0
	BEGIN
		IF CHARINDEX(@Delimiter, @HList) > 0
		   BEGIN
			SELECT @FOIDtext =LEFT(@HList, CHARINDEX(@Delimiter, @HList)-1)
		   END
		ELSE	
		   BEGIN
		 	SELECT @FOIDtext = RTRIM(LTRIM(@HList))
		   END
		--Insert into Variable Table
		INSERT INTO @ListTable(Field1)
		       SELECT RTRIM(LTRIM(@SOURCEtext ))
		--Remove Item from list
		SELECT @HList = RIGHT(RTRIM(@HList), LEN(RTRIM(@HList)) -
CHARINDEX(@Delimiter, @HList))
	END	
	RETURN
END


-- 
-Francisco
http://pcthis.blogspot.com | PC news with out the jargon!