Re: Pb searching a word in a string

pdemeyer@tmphard.univ-lr.fr wrote: > > Here is the problem: > Using OInformix, 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). It can't be done! Ok, it can be done, but using OR and MATCHES like so: SELECT * FROM table WHERE (col MATCHES "exam[^A-Za-z0-9]*" OR col MATCHES "*[^A-Za-z0-9]exam[^A-Za-z0-9]*" OR col MATCHES "*[^A-Za-z0-9]exam") OR what about: SELECT * FROM table WHERE col MATCHES "*exam*" AND col NOT MATCHES "*exam[A-Za-z0-9]*" AND col NOT MATCHES "*[A-Za-z0-9]exam[A-Za-z0-9]*" AND col NOT MATCHES "*[A-Za-z0-9]exam*" But I think this still equates to OR. Try them for performance, I would be interested to hear. Cheers, -- Mark. +-------------------------------------------------------------------------+ |Mark D. Stock - The West Solutions Group http://www.west.co.za | | | |mailto:marks@west.co.za +------------------------------------------------+ |Tel: +27 11 803 2151 |If it doesn't work... force it! | |Fax: +27 11 803 2189 |If it breaks... it needed replacing anyway! | |Cell: +27 83 250 2325 |Well, that's how I code anyway! | +------------------------+------------------------------------------------+