Mysql Regexp To Match The Whole Word Only

How can I match the whole word in mysql? For instance, if I type in 'lau', I don't want to match 'laura' or 'laurance'. Below is my working query that matches 'lau' with 'laura' SE

Solution 1:

Let's break down your current regexp:

>[^<]*lau

>match the character'>'
[^<]*match0or more non-'<' characters
lau    match'lau'

So this would match the following:

>bbblau>bbb<lau>lau><lau

I assume what you want is to match the following:

>lau

But not:

>laubbb><lau>bbb<lau

In that case, the following regexp would work:

>lau( |<|$)

Edit: using the method shown by andrewsi might be cleaner:

>lau[[:>:]]

Solution 2:

Does this work?

REGEXP '[^<]*lau[[:>:]]'

According to the documentation, [[:>:]] will match a word boundary at the end of a word

Solution 3:

andrewsi code didn't work for me, but this REGEXP solved my problem.

m_city REGEXP '[[:<:]]YOUR_CITY[[:>:]]'