Re: Pb searching a word in a string

pdemeyer_at_tmphard.univ-lr.fr wrote:
>
> Hi there!
>
> Here is the problem:
> Using Oracle 7, what is the trick to find a word in a string, but only THIS
> word. An example would be better :
>
> My fields are : "i flunked my exam" and "this is an example"
> If i want to find fields which contain the word 'exam', with the simple syntax
> %exam%, it will find the 2 fields, but I don't want the second one here.
>
> The difficulty here is to find the trick which works in every case,
> even if the searched word is at the beginning or at the end of the string,
> thinking that we could have other word separators than the space character
> (for example a comma character, the ':' character ...) and without using AND
> or OR clause in the select request (cauz the database is really huge, and I
> must optimize everywhere).
>
> Any help would be appreciated (even if you tell me that we can't do this).
> Thanx a lot
>
> -----------------------
> Patrice
> pdemeyer_at_cri.univ-lr.fr

Try the character function INSTR(). See SQL Language Reference Manual, Chapter 3: Functions. With your example:

        SELECT * FROM TAB WHERE INTR(COL,'exam') > 0

Gilles.
gillesc_at_speedware.com Received on Fri Sep 27 1996 - 00:00:00 CEST