使 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屋!