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?
-
I see you un-accepted my answer; what went wrong?NullUserException– NullUserException2010-09-12 04:40:02 +00:00Commented Sep 12, 2010 at 4:40
Add a comment
|
3 Answers
Use this one:
select * from users where name like '% LENA %' OR name like 'LENA %' OR name like '% LENA' OR name like 'LENA'
Try it, it will work, and too simple with 2 spaces at start and end. Its long but fast query. You can also use Full Text Search, Concat, Regex ... but for me, I like simple, just add some space before %.
However, you can try shorter but slower query:
SELECT * FROM users WHERE name REGEXP '(^|\s+)LENA($|\s+)'
Comments
You could use REGEXP:
SELECT fields
FROM users
WHERE name REGEXP '(^|\s+)LENA($|\s+)'
answered Sep 12, 2010 at 0:43
NullUserException
85.8k31 gold badges212 silver badges239 bronze badges
Comments
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.
3 Comments
Bob Fanger
Full Text Search would do the trick, but your "something like" query will give identical results as the original (but with more cpu overhead).
Martin Smith
@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.Bob Fanger
Looked up '%' in the MySQL manual and it also matches
zero characters. This was the flaw in my logic.