使 MySQL LIKE 仅返回完整的单词匹配 [英] Make MySQL LIKE Return Full Word matches Only

问题描述

我不希望在 LIKE 匹配部分单词的地方返回行.我在空格上拆分字符串，然后生成一个会找到匹配项的查询，但它返回部分单词的匹配项.这是一个例子

I dont want rows to be returned where the LIKE is matching a partial word. I am splitting strings on whitespace and then generating a query that will find a match, but its returning matches for partial words. Here is an example

SELECT ID from VideoGames WHERE Title Like "%GI%" AND Title Like "%JOE%"

返回一个匹配，其中 title = "Yu-Gi-Oh! Power of Chaos: Joey the Passion".

Returns a match where title = "Yu-Gi-Oh! Power of Chaos: Joey the Passion".

我知道只匹配完整的单词并不能完全解决问题，但它会大大提高准确性.我该怎么做才能返回我想要的而不是这个.

I know only matching full words wont completely resolve the issue, but it will hugely increase accuracy. What can i do to return what i want rather than this.

推荐答案

您可以使用 RLIKE(LIKE 的正则表达式版本)来获得更大的匹配灵活性.

You can use RLIKE, the regular expression version of LIKE to get more flexibility with your matching.

SELECT ID from VideoGames 
    WHERE Title RLIKE "[[:<:]]GI[[:>:]]" AND Title RLIKE "[[:<:]]JOE[[:>:]]"

[[:<:]] 和 [[:>:]] 标记是分别标记单词开头和和的单词边界.您可以构建单个正则表达式而不是 AND，但我已将其与您的原始问题相匹配.

The [[:<:]] and [[:>:]] markers are word boundaries marking the start and and of a word respectively. You could build a single regex rather than the AND but I have made this match your original question.

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