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'

SELECT *
FROM
(
    SELECT

        p.page_id,
        p.page_url AS url,
        p.page_title AS title,
        SUBSTRING_INDEX(p.page_content_1, ' ', 500) AS content,
        EXTRACT(DAY FROM p.page_backdate) AS date,
        EXTRACT(MONTH FROM p.page_backdate) AS month,
        EXTRACT(YEAR FROM p.page_backdate) AS year

    FROM root_pages AS p

    WHERE p.page_hide != '1'
    AND p.page_url != 'cms'
    AND p.page_url != 'search'

    ORDER BY p.page_created DESC
) x
WHERE x.content REGEXP  '>[^<]*lau'

Any idea to fix my REGEXP?

The reason I use REGEXP '>[^<]*searchtext' is that my contents have html tags, such as,

<p class="heading-sitemap">About us</p>
<ul>
<li class="subheading-sitemap">The team</li>
<li><a href="#">Introduction to gt</a></li>
<li><a href="#">Simon x</a></li>
<li><a href="#">Cathrin xe</a></li>
<li><a href="#">Patrick x</a></li>
<li><a href="#">Laurence x</a></li>
<li><a href="#">Tessa x</a></li>>
</ul>

I don't want to search the word may fall inside the tags such as <a href="http://laura.com">.

回答1:

Does this work?

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

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

回答2:

Let's break down your current regexp:

>[^<]*lau

>      match the character '>'
[^<]*  match 0 or 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[[:>:]]

回答3:

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

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