CODEWITHSUNDEEP
mysqlsql
Teej
Teej

Reputation: 12873

MySQL Regex in Where

Search the products with '^wall$', '\swall\s', '^wall ' or ' wall$' in its name. But it should not have results like 'wallpaper' or 'wonderwall'

SELECT * 
FROM `products` 
WHERE (products.name REGEXP 'wall?[:space]')  
ORDER BY products.updated_at DESC

So far the above obviously doesn't work. What should be the correct way to do this.

Updated the spec for clearer explanation.

Upvotes: 1

Views: 1291

Answers (3)

Aprillion
Aprillion

Reputation: 22324

use TRIM(products.name) = 'wall' instead of a regex...

after question update, use mysql equivalent of \bwall\b regex (word boundaries):

'[[:<:]]word[[:>:]]'

Upvotes: 2

huysentruitw
huysentruitw

Reputation: 28111

Can you try this REGEXP '[[:<:]]wall[[:>:]]'

Upvotes: 2

Mircea Soaica
Mircea Soaica

Reputation: 2817

SELECT * 
FROM `products` 
WHERE (products.name REGEXP '[[:<:]]wall[[:>:]]')  
ORDER BY products.updated_at DESC

Upvotes: 5

Related Questions