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'.
Asked
Active
Viewed 175 times
5 Answers
1
Looks like this is the same as: Search for "whole word match" in MySQL
The approach uses a slick regular expression.
Community
- 1
- 1
BrettFromLA
- 906
- 1
- 7
- 17
-
1This 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. – Hazzit Jan 30 '14 at 00:39
-
@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 + ' ' . – BrettFromLA Jan 30 '14 at 00:49
0
Assuming a word is defined by a space, you can do:
where concat(' ', col, ' ') like '% John %'
Gordon Linoff
- 1,242,037
- 58
- 646
- 786
0
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 %"
Hazzit
- 6,782
- 1
- 27
- 46
0
You can use REGEXP function.
Try this:
SELECT *
FROM tableA a
WHERE a.name REGEXP '[[:<:]]John[[:>:]]'
Saharsh Shah
- 28,687
- 8
- 48
- 83