Query for exact match of a string in SQL

Question

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

When I write the query as

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

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".

For this I did as:

select description from tproduct where description = 'diamond'

But it is giving an error:

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

The description column contains:

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.

Answer 1

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.

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[[:>:]]';

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

Answer 2

You can use a regular expression with a special pattern for word boundaries.

select description from tproduct 
where description regexp '[[:<:]]diamond[[:>:]]'

See REGEXP.