Subject Re: [firebird-support] Question about "IN"
Author Helen Borrie
At 06:21 PM 4/01/2004 -0500, you wrote:

>Hi all,
>I apologize, I should have been more specific. I want to be able to
>search text in a field to see if it contains keyword for a search. I
>figured that varhcar(250) could hold a description of a product and that
> could see if the user supplied term was contained anywhere on the field.

You can use CONTAINING for this but it's a fairly blunt instrument. With
keywords you generally want a case-insensitive search and you probably want
to make a semantic distinction to avoid returning irrelevant rows.

>Should I instead, use a separate table with kewords linked to my
>products table?

That's what I do - with a 1:many relationship between the row in the
searched table and the rows in the keyword table. For a working example,
see the online FAQ at the site - the keyword search at
the right uses this structure. The downside is that you have to maintain
the keywords, i.e. provide users with a utility to do that.

As I know you have IBO, you might also explore the Full Text ("fuzzy")
search components... they let you automate the maintenance of searchable
criteria globally - not just keywords, but stuff like metaphones and
soundexes, synonyms, antonyms, and so on, if you want them. You can set up
global exclusion lists for keyword creation, too. (words like 'and', 'the',