在SQL中查询字符串的完全匹配 [英] Query for exact match of a string in SQL

问题描述

我想从数据库表中查询字符串的完全匹配.

I want to query for the exact match of a string from a database table.

当我将查询写为

select description from tproduct where description like '%diamond%'

它运行并以wild card查询的形式给出结果.例如，我有钻戒，钻石大师等.

it runs and gives the results as a wild card query. For example, I got ringdiamond, diamondmaster, etc.

但是我只想获得钻石".

But I want to get only "diamond".

为此，我这样做了:

select description from tproduct where description = 'diamond'

但是它给出了一个错误:

But it is giving an error:

错误:"where子句"中的未知列"diamond"

Error: Unknown column 'diamond' in 'where clause'

说明列包含:

This bracelet is composed of round diamond surrounded by milgrain detailing. Its secure setting prevents it from twisting and ensures it will sit impeccably on her wrist.


This stylish design is the perfect accessory for the day or evening. Akoya cultured pearls are lined up by a string of bezel-set round diamond.

推荐答案

如果我正确地理解了这个问题，那么您希望匹配钻石"，因为它是一个不同的词，而不是另一个词，例如"diamondville".您可以执行类似SELECT * FROM tproduct WHERE描述的操作，例如'％diamond％'，这将匹配所有用空格包围钻石"的记录.

If I understand the question correctly you want to match "diamond" when it's a distinct word and not part of another word like "diamondville." You could do something like SELECT * FROM tproduct WHERE description like '% diamond %' and this would match all of the records which have "diamond" surrounded by spaces.

但这是行不通的.找不到描述以"Diamond"开头的记录，或者"Diamond"之后有逗号或句点的记录.

But that wouldn't work. That wouldn't find records where the description starts with "Diamond" or where there's a comma or period after "Diamond"

您需要匹配一个正则表达式.您可以使用以下方式指定单词边界:

You need to match on a regular expression. You can specify word boundaries with that:

select * from t2 where description regexp '[[:<:]]diamond[[:>:]]';

有关MySQL正则表达式的更多信息，请参见此页面: http://dev.mysql.com/doc/refman/5.1/zh-CN/regexp.html

See this page for more info on MySQL regular expressions: http://dev.mysql.com/doc/refman/5.1/en/regexp.html

这篇关于在SQL中查询字符串的完全匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！