Searching in a varchar field

[Sumit]

I have a varchar column which containd comma delimited values like
Rec# Fruits
1 Apple, Peach, Strawberry
2 Orange, Mango
3 Banana, Grape
...........

Now i have to add search facility so that a user could search for more than 1 fruit at a time. I have a Stored Procedure which returns records from this table. that SP has a Parameter @SearchFruit Varchar(500) and the user could pass in values like 'Apple, Mango' to this parameter.

Now how should i write the SQL so that i get back the records Rec# 1 & 2 since apple is there in 1st record and mango is there in the 2nd ??

I know if a put the comma delimited values as individual records in a temporary table and also do the same for the parameter values then i can get the desired results. But i want to avoid doing that. Any other way ?


Thanks
Sumit.

[Zoey]

Sumit,
It would be pretty easy to just have a while loop and separate the string that was passed in to the SP. Then you can use 'LIKE' and dynamically build the string to get you both the rows
Rozina
------------
Sumit at 7/12/01 12:06:48 PM


I have a varchar column which containd comma delimited values like
Rec# Fruits
1 Apple, Peach, Strawberry
2 Orange, Mango
3 Banana, Grape
...........

Now i have to add search facility so that a user could search for more than 1 fruit at a time. I have a Stored Procedure which returns records from this table. that SP has a Parameter @SearchFruit Varchar(500) and the user could pass in values like 'Apple, Mango' to this parameter.

Now how should i write the SQL so that i get back the records Rec# 1 & 2 since apple is there in 1st record and mango is there in the 2nd ??

I know if a put the comma delimited values as individual records in a temporary table and also do the same for the parameter values then i can get the desired results. But i want to avoid doing that. Any other way ?


Thanks
Sumit.

[Sumit]

Thanks Zoey.
That is one of the solutions but will it be ok considering the performance issues. If the user passes in lets say 5 comma delimited values to the parameter then my SQL would be something like this :-

Where (Fruits Like '%' + @1stValue + '%&#39
or (Fruits Like '%' + @2ndValue + '%&#39 ... and so on for 5 values...

The problem is that i can't even use IN clause here. Let me just try this solution and see how the perfomance is.

Thanks once again.

Sumit.



------------
Zoey at 7/12/01 6:40:43 PM

Sumit,
It would be pretty easy to just have a while loop and separate the string that was passed in to the SP. Then you can use 'LIKE' and dynamically build the string to get you both the rows
Rozina
------------
Sumit at 7/12/01 12:06:48 PM


I have a varchar column which containd comma delimited values like
Rec# Fruits
1 Apple, Peach, Strawberry
2 Orange, Mango
3 Banana, Grape
...........

Now i have to add search facility so that a user could search for more than 1 fruit at a time. I have a Stored Procedure which returns records from this table. that SP has a Parameter @SearchFruit Varchar(500) and the user could pass in values like 'Apple, Mango' to this parameter.

Now how should i write the SQL so that i get back the records Rec# 1 & 2 since apple is there in 1st record and mango is there in the 2nd ??

I know if a put the comma delimited values as individual records in a temporary table and also do the same for the parameter values then i can get the desired results. But i want to avoid doing that. Any other way ?


Thanks
Sumit.

[Todd]

Any chance normalizing the data is an options?


------------
Sumit at 7/12/01 12:06:48 PM


I have a varchar column which containd comma delimited values like
Rec# Fruits
1 Apple, Peach, Strawberry
2 Orange, Mango
3 Banana, Grape
...........

Now i have to add search facility so that a user could search for more than 1 fruit at a time. I have a Stored Procedure which returns records from this table. that SP has a Parameter @SearchFruit Varchar(500) and the user could pass in values like 'Apple, Mango' to this parameter.

Now how should i write the SQL so that i get back the records Rec# 1 & 2 since apple is there in 1st record and mango is there in the 2nd ??

I know if a put the comma delimited values as individual records in a temporary table and also do the same for the parameter values then i can get the desired results. But i want to avoid doing that. Any other way ?


Thanks
Sumit.

[Todd]

I saw this recently on sqlteam.com

This will denormalize your table(my recommendation would be to do this permenantly if possible). You can work out a where clause from there. No promises about performance, but it may beat the while loop.

-- CREATE TABLE #Fruits(
-- ID INT IDentity(1,1),
-- FruitStr VARCHAR(150))
-- INSERT #Fruits(FruitStr) VALUES('Apple, Peach, Strawberry&#39
-- INSERT #Fruits(FruitStr) VALUES('Orange, Mango&#39
-- INSERT #Fruits(FruitStr) VALUES('Banana, Grape&#39
DECLARE @SearchFruit VARCHAR(500)

SET @SearchFruit = 'Apple, Mango'

SELECT ID, A.Fruit
FROM (SELECT ID,
LTRIM(RTRIM(NULLIF(SUBSTRING(',' + b.FruitStr + ',' , TALLY , CHARINDEX(',' , ',' + b.FruitStr + ',' , TALLY) - TALLY) , '&#39)) as "Fruit"
FROM #Fruits B
--Creates Table of Number 1-8000
cross join (SELECt Distinct A.c1 +b.C1+.c.c1 +d.c1 AS "Tally"
FROM (SELECT 1 as "C1" UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 0)AS A
CROSS JOIN (SELECT 10 as "C1" UNION SELECT 20 UNION SELECT 30 UNION SELECT 40 UNION SELECT 50 UNION SELECT 60 UNION SELECT 70 UNION SELECT 80 UNION SELECT 90 UNION SELECT 0)AS B
CROSS JOIN (SELECT 100 as "C1" UNION SELECT 200 UNION SELECT 300 UNION SELECT 400 UNION SELECT 500 UNION SELECT 600 UNION SELECT 700 UNION SELECT 800 UNION SELECT 900 UNION SELECT 0)AS c
CROSS JOIN (SELECT 1000 as "C1" UNION SELECT 2000 UNION SELECT 3000 UNION SELECT 4000 UNION SELECT 5000 UNION SELECT 6000 UNION SELECT 7000 UNION SELECT 0)AS D
WHERE A.c1 +b.C1+.c.c1 +d.c1 < (SELECT MAX(LEN(FruitStr)) + 2 from #Fruits) ) AS C
WHERE TALLY <= LEN(&#39;,&#39; + b.FruitStr + &#39;,&#39 AND SUBSTRING(&#39;,&#39; + b.FruitStr + &#39;,&#39; , TALLY - 1, 1) = &#39;,&#39;
AND CHARINDEX(&#39;,&#39; , &#39;,&#39; + b.FruitStr + &#39;,&#39; , TALLY) - TALLY > 0 ) AS A


------------
Sumit at 7/12/01 12:06:48 PM


I have a varchar column which containd comma delimited values like
Rec# Fruits
1 Apple, Peach, Strawberry
2 Orange, Mango
3 Banana, Grape
...........

Now i have to add search facility so that a user could search for more than 1 fruit at a time. I have a Stored Procedure which returns records from this table. that SP has a Parameter @SearchFruit Varchar(500) and the user could pass in values like &#39;Apple, Mango&#39; to this parameter.

Now how should i write the SQL so that i get back the records Rec# 1 & 2 since apple is there in 1st record and mango is there in the 2nd ??

I know if a put the comma delimited values as individual records in a temporary table and also do the same for the parameter values then i can get the desired results. But i want to avoid doing that. Any other way ?


Thanks
Sumit.