CODEWITHSUNDEEP
phpmysqlwildcard
bajskorven
bajskorven

Reputation: 21

php mysql search query wildcard problem

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?

Upvotes: 2

Views: 2014

Answers (3)

Ken Le
Ken Le

Reputation: 1805

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+)'

Upvotes: 4

Martin Smith
Martin Smith

Reputation: 452988

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.

Upvotes: 1

NullUserException
NullUserException

Reputation: 85458

You could use REGEXP:

SELECT fields 
  FROM users
 WHERE name REGEXP '(^|\s+)LENA($|\s+)'

Upvotes: 1

Related Questions