Posted by Hugo Kornelis on 06/17/05 23:59

On 17 Jun 2005 03:56:57 -0700, Axel wrote:

>My approch concatenates all candidate fields into one
>large string and then looks for the search string within that long
>string.
(snip example)

Hi Axel,

The example clarifies what you want to do.

First, revisit this page: http://www.sommarskog.se/arrays-in-sql.html.
It will explain how you can break the collected search words ("screw
iron 17 outdoor" in your example) into a set of rows.

To find the Products that match at least one of the terms given, use

SELECT DISTINCT p.Product
FROM Products AS p
INNER JOIN #SearchWords AS s -- The search words in a table;
-- See link above for details.
ON p.Name + p.Desc + p.Comment + p.Category + p.ID
LIKE '%' + s.Word + '%'

And if you only want the products that match ALL the terms given:

SELECT p.Product
FROM Products AS p
INNER JOIN #SearchWords AS s -- The search words in a table;
-- See link above for details.
ON p.Name + p.Desc + p.Comment + p.Category + p.ID
LIKE '%' + s.Word + '%'
GROUP BY p.Product
HAVING COUNT(*) = (SELECT COUNT(*) FROM #SearchWords)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Back to original message]