If you want to match only "(johnie)" or " johnie ", then you could use a regular expression test. They can get as complicated as your brain will tolerate.
Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com > -----Original Message----- > From: thomas Armstrong [mailto:[EMAIL PROTECTED] > Sent: Thursday, September 20, 2007 3:52 AM > To: Michael Dykman > Cc: mysql@lists.mysql.com > Subject: Re: Query to find "foo" within "(foo)" > > Thank you Michael for your answer. > > On 9/19/07, Michael Dykman <[EMAIL PROTECTED]> wrote: > > The whitespace counts... try LIKE '%johnie%' (or better > '_johnie_' .. > > the underscorematches any single character). > I want to match '(johnie)' and not 'johnies' or 'aljohnier', what it's > the query does with '_'. > If I include whitespaces, I match only the word 'johnie'. > > But I've got some texts like: > * (johnie) > * johnie-sullivan > * johnie.sullivan > and I want to find them. I suppose the right way is create > another condition: > firstname LIKE '%(johnie %' OR '% johnie)%' OR '%(johnie)%' > OR '%.johnie %' ... > > > Bear in mind that, with > > a LIKE variable as the first element in your string, this query will > > do a full table scan every time. If you get a lot of > records in here, > > that's going to kill you. > You're right, but I didn't find another better way. What do you think > it's better? > > > > > - michael > > > > > > On 9/19/07, thomas Armstrong <[EMAIL PROTECTED]> wrote: > > > Hi. > > > > > > I've got this table in mySQL: > > > > > > item 1: > > > -- firstname: John (Johnie) > > > -- phone: 555-600-200 > > > > > > item 2: > > > -- firstname: Peter > > > -- phone: 555-300-400 > > > > > > I created this SQL query to find 'johnie': > > > ------------------------ > > > SELECT friends.id FROM friends WHERE ((friends.firstname LIKE '% > > > johnie %' OR friends.firstname LIKE 'johnie %' OR > friends.firstname > > > LIKE '% johnie' OR friends.firstname LIKE 'johnie' OR > friends.phone > > > LIKE '% johnie %' OR friends.phone LIKE 'johnie %' OR > friends.phone > > > LIKE '% johnie' OR friends.phone LIKE 'johnie')) ORDER BY > > > friends.firstname LIMIT 0, 9999 > > > ------------------------ > > > > > > But it doesn't match anything, because it's considers > "(johnie)" as a > > > single word. Is there any way to make mySQL consider "(johnie)" as > > > "johnie". > > > > > > I know I can create another condition within my query: > > > firstname LIKE '(johnie)' OR firstname LIKE '(johnie%' OR > firstname > > > LIKE '%johnie)' > > > but I also might consider other characters like ' " - * > > > > > > Any suggestion? > > > > > > -- > > > MySQL General Mailing List > > > For list archives: http://lists.mysql.com/mysql > > > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > > > > > > > > > > > -- > > - michael dykman > > - [EMAIL PROTECTED] > > > > - All models are wrong. Some models are useful. > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
