If I search for LENA, I only want to get results where LENA is one word and not part of a word like Lenason or Anna-Lena or Malena
How do I write such a query, not
"select * from users where name like '%LENA%'"
What should it be instead?
views:
37answers:
2
+1
A:
You could use REGEXP:
SELECT fields
FROM users
WHERE name REGEXP '(^|\s+)LENA($|\s+)'
NullUserException
2010-09-12 00:43:35
+1
A:
You might be better off looking into Full Text Search for this.
Otherwise I think you're stuck doing something like this
"select * from users WHERE CONCAT(' ',name, ' ') like '% LENA %'"
Which will be pretty inefficient as it requires a full table scan.
Martin Smith
2010-09-12 00:44:11
**Full Text Search** would do the trick, but your "something like" query will give identical results as the original (but with more cpu overhead).
Bob Fanger
2010-09-12 11:34:54
@Bob - You were right that there was an issue with my query but not the one you were thinking of I think. I forgot that you needed to use `CONCAT` for string concatenation in MySQL rather than `+`. The reason for concatenating spaces is that names where `lena` is the first, last or only word wouldn't match the where clause `name like '% LENA %'` without this.
Martin Smith
2010-09-12 12:14:44
Looked up '%' in the MySQL manual and it also matches `zero characters`. This was the flaw in my logic.
Bob Fanger
2010-09-12 13:14:23