From: thomas Armstrong (tarmstronggmail.com)
Date: Thu Sep 20 2007 - 02:52:14 CDT

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

Thank you Michael for your answer.

On 9/19/07, Michael Dykman <mdykmangmail.com> 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 <tarmstronggmail.com> 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/mysql?unsub=mdykmangmail.com
> >
> >
>
>
> --
> - michael dykman
> - mdykmangmail.com
>
> - All models are wrong. Some models are useful.
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]