MySQL Regex in Where

Question

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.

Your spec is inconsistent. E.g. wallpaper matches products with "wall". Please can you be clearer about what you are trying to achieve? Are you attempting to match Any string where "wall" appears as a single word? — RB.
– RB., Commented Apr 2, 2012 at 11:55
That's the problem that I am having. "wallpaper" should not be matched. It should be either nothing at the end or in front or just space in front or at the end. — Teej
– Teej, Commented Apr 2, 2012 at 11:58

Mircea Soaica · Accepted Answer · 2012-04-02 11:58:34Z

5

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

answered Apr 2, 2012 at 11:58

Mircea Soaica

2,8171 gold badge17 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mircea Soaica Over a year ago

[[:<:]] and [[:>:]] are special metacharacters that match Start of word and End of word. There probably are also other special metacharacters that could have been used (like \b)

huysentruitw · Accepted Answer · 2012-04-02 12:00:56Z

2

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

answered Apr 2, 2012 at 12:00

huysentruitw

28.2k10 gold badges95 silver badges141 bronze badges

Comments

Aprillion · Accepted Answer · 2012-04-02 12:10:18Z

2

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

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

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

edited Apr 2, 2012 at 12:10

answered Apr 2, 2012 at 11:58

Aprillion

22.4k6 gold badges59 silver badges94 bronze badges

1 Comment

safarov Over a year ago

product name can be "wall flower" for example. this condition is not true

Collectives™ on Stack Overflow

MySQL Regex in Where

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related