CODEWITHSUNDEEP
mysqlsql
Romi
Romi

Reputation: 4921

Query for exact match of a string in SQL

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.

Upvotes: 5

Views: 57152

Answers (5)

Srinu Babu
Srinu Babu

Reputation: 422

For SQL Server: Description
SELECT description FROM tproduct WHERE description ='diamond' COLLATE SQL_Latin1_General_CP1_CS_AS

Upvotes: 0

ratsbane
ratsbane

Reputation: 890

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

Upvotes: 9

Mudassir
Mudassir

Reputation: 187

select description from tproduct where description = 'diamond'

Upvotes: -2

Bill Karwin
Bill Karwin

Reputation: 562270

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

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

See REGEXP.

Upvotes: 3

gmhk
gmhk

Reputation: 15940

select description from tproduct where description like 'diamond'

Upvotes: 0

Related Questions