Query to find "foo" within "(foo)"

SQL queries don't match words next to Parentheses

[guarriman]

Hi.

I've got this table in mySQL:

item 1:
-- firstname: John (Johnie)
-- phone: [phone]

item 2:
-- firstname: Peter
-- phone: [phone]

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 consdiers "(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?

[d40sithui]

i duno if this is it, but it seems to me that you should NOT have a space in the "% johnie %" search. try without the spaces "%johnie%" and see how it works.

[guarriman]

Nope. If I add '%johnie%', it would match with 'johnies', 'aljohnie' or 'myjohnieos' and I don't want it. Blanks are mandatories.

[Habtom]

From the top of my mind, I think something like this might work:

'% [(]johnie[)] %'

Habtom

[vincevincevince]

WHERE friends.phone REGEXP '[[:<:]]johnie[[:>:]]';