Help ! search question...

Hi all,

I'd like to ask about how to conduct a search in Oracle which does the following:

1. If the user enters a single word, such as "IBM", retrieve all records which contain the string "ibm" as a standalone word.
2. If the user enters a single word plus "*" (eg. "IBM*") , retrieve all records which contain a word which starts with the letters "IBM", but is not necessarily the entire word.

Is there a way this can be done with regular SQL?

Barring that, my first thought was to to a select on "%IBM%", and then do a perl regex search on it using the oroinc.com package. But this would require two "passes", and since the database contains 300,000 records, I don't know yet what kind of performance hit this would involve.

The second idea is to use the Oracle ConText Cartridge (Intermedia Text in 8i). I think we may end up going this way, but I'd like to avoid it if possible because it apparently has a history of problems.

The third idea is to use a package called "Omnidex". I know very little about it.

Yet another idea is to use something called the "OWS" package, which includes the "OWA_PATTERN" package. Again, I don't know anything about it.

Does anyone have suggestions? This is unfamiliar territory for me, and we're in a time crucnch.

Thanks,
Jack

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Sep 15 2000 - 20:52:48 CDT