The name column contains items like 'John Smith', 'Elsa John Sanders', 'LilJohn Thomson', 'John'. How can I structure a query just to return the names with John but not LilJohn.
I cannot do a LIKE '%John%' as it would return 'LilJohn Thomson'.
Add a comment
|
5 Answers
Looks like this is the same as: Search for "whole word match" in MySQL
The approach uses a slick regular expression.
2 Comments
Hazzit
This indeed is a good link. Sometimes it seems though if all you have is a hammer, everything starts looking like a nail: Regular expressions are great but relatively slow, because they usually cannot make use of indexes. If the names are always enclosed in spaces, I don't see a need for a regular expression.
BrettFromLA
@Hazzit Good call on regular expressions being relatively slow. If names are enclosed in spaces that's a good solution ... but often the name comes at the start or end of the value. To work around that, I would check against ' ' + VALUE + ' ' .
Assuming a word is defined by a space, you can do:
where concat(' ', col, ' ') like '% John %'
Comments
The following expression should find all "John"s
a = 'John' OR
a LIKE 'John %' OR
a LIKE '% John %' OR
a LIKE '% John'
Please note that some other tricks also work, but may severely hit performance, like
CONCAT(" ",a," ") LIKE "% John %"
Comments
Try SIMILAR TO '%+John+%' using the + space wildcard.
Comments
You can use REGEXP function.
Try this:
SELECT *
FROM tableA a
WHERE a.name REGEXP '[[:<:]]John[[:>:]]'