Subject RE: [firebird-support] Question about "IN"
Author Nigel Weeks
> 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.
> Should I instead, use a separate table with kewords linked to my
> products table?

Just use the standard methods for searching for text in a field:
Select * from table where field CONTAINING 'term'; (slow, case-insensitive)
Select * from table where field LIKE '%term%'; (slow, case-sensitive)
Select * from table where field LIKE 'term%'; (fast, uses index)

Note: At the moment, these require a brute-force scan of all records, as
indexes aren't used for 'CONTAINING', and 'LIKE' with a wildcard as the
first character.
There have been modifications to massively improve this searching, but it's
still in development.

If you need high-speed part-string matching(try 'LIKE' and 'CONTAINING'
first...), then, as you suggested, break individual words out into another
table, reference each record back to the original table, index that
field(it'll only have one word in the field), put an index on it, and use
exact matches.