MySQL REGEXP仅匹配整个单词 [英] MySQL REGEXP to match the whole word only

问题描述

如何在mysql中匹配整个单词?

How can I match the whole word in mysql?

例如，如果我输入" lau "，则我不想匹配"劳拉"或"劳伦斯(Laurance)".

For instance, if I type in 'lau', I don't want to match 'laura' or 'laurance'.

以下是我的工作查询，该查询将" lau "与"劳拉"

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'

有什么办法解决我的REGEXP吗?

Any idea to fix my REGEXP?

我使用REGEXP '>[^<]*searchtext'的原因是我的内容带有html标签，例如

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>

我不想搜索可能落在诸如<a href="http://laura.com">之类的标签内的单词.

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

推荐答案

这项工作有效吗?

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

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

根据文档，[[:>: ]]将匹配单词结尾处的单词边界

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

这篇关于MySQL REGEXP仅匹配整个单词的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！