string pattern matching informix

I am looking for a way to see if a string belongs to a list of patterns in the database. For example, if i have the following list in informix: mike bob jay david lisa I want to be able to say 'billybob' is a match for the list since it contains word 'bob'. This is a reverse of a usual usage of string matching. Usually, you ask for a string in database that matches a particular pattern. Here, I want to ask for a pattern in the database that matches a particular string. I would really appreciate any help/idea.

jaehocho@gmail.com wrote: > I am looking for a way to see if a string belongs to a list of patterns > in the database. For example, if i have the following list in informix: > > mike > bob > jay > david > lisa > > I want to be able to say 'billybob' is a match for the list since it > contains word 'bob'. This is a reverse of a usual usage of string > matching. Usually, you ask for a string in database that matches a > particular pattern. Here, I want to ask for a pattern in the database > that matches a particular string. It would be helpful if you gave even outline schemas. Since you didn't, you get mine to play with... CREATE TABLE Patterns(word VARCHAR(10) NOT NULL PRIMARY KEY); CREATE TABLE RealData(id_number SERIAL NOT NULL PRIMARY KEY, stringVARCHAR(255) NOT NULL); SELECT R.ID_number FROM RealData R, Patterns P WHERE R.String MATCHES ("*" || P.Word || "*"); I'm not convinced the parenthese are necessary - but they make it clearer to me... It's a pretty esoteric join, but nonetheless a join condition. If you stored the patterns with leading and/or trailing stars (or not), you can make your searches cleverer. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2005.01 -- http://dbi.perl.org/