Re: Search for a word in all columns in all rows

diljaey_at_comcast.net (Dil Jaey) wrote in message news:<d8e274e1.0401220746.268c002b_at_posting.google.com>...
> I am allowing users to enter in a keyword search via a web interface
> and in the backend Oracle 9i is requested to search for that keyword
> in all columns (30) for every row (thousands) that exists within a
> given table(s).
>
> Is this a relatively simple task to perform or is this a path that
> should not be traveled.
>
> Thanks,
> DJ

sounds like a poor design to me, you'll have a WHERE clause with 30 lines of
AND column_x like '%keyword%'

 Can't think of a good solution quickly. Adding a search column or table?
table FIND_WORD
other_key foreign key references primary key of the original table with the 30 columns
key_word

select original_table.*
from original_table, FIND_WORD
where FIND_WORD.key_word = <webpage value>   and FIND_WORD.other_key = original_table.key_column;

populating and maintaining the FIND_WORD table adds some complexity to your application.

hth
  Ed Received on Thu Jan 22 2004 - 13:14:06 CST