Re: Pb searching a word in a string

Even when the syntax errors are corrected, this would match words which end "exam" (I can't think of one, but other searches for other words could easily match the suffix of a word -- eg if looking for "fix"), and it doesn't match "exam" at the very end of the string. You really need a 3-way match: select * from my_tab where (search_col matches "exam[: ,]*" -- at start or search_col matches "*[: ,]exam[: ,]*" -- in middle or search_col matches "*[: ,]exam") -- at end Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h> }From: ellerbe@qsun.ho.att.com }Date: Thu, 26 Sep 1996 08:36:02 -0400 }X-Informix-List-Id: <list.11490> } }If all word separators are known upfront then }something like this should work: } } select * from my_tab } where search_col matches *exam[: ,]*. } }Here the comma, semi-colon and space character }are explicited included as part of string } }} From: pdemeyer@tmphard.univ-lr.fr }} Date: 26 Sep 1996 07:12:36 GMT }} X-Informix-List-Id: <news.28482> }} }} Here is the problem: }} Using Informix, 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).