5

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.
Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
Romi
  • 4,833
  • 28
  • 81
  • 113
  • 1
    Are you sure you didn't use double quotes by accident? – John Gibb Jun 28 '11 at 05:50
  • @John Gibb: Yeah, i am very sure. i used only single quotes – Romi Jun 28 '11 at 05:52
  • If you want to use `LIKE`, just use it without the wildcard entries, `select description from tproduct where description like 'diamond'` – Balanivash Jun 28 '11 at 05:53
  • @Balanivash: i tried it too , but is not working :( – Romi Jun 28 '11 at 05:54
  • @Romi : is this the full query you are running?? cos both the above queries work perfectly for me. – Balanivash Jun 28 '11 at 05:55
  • This kind of error is usually backticks (`) instead of single quotes ... – Jacob Jun 28 '11 at 05:55
  • @ Balanivash: yeah this the full query, and i am running it through SQuirrel SQL Client version 2.6.9 – Romi Jun 28 '11 at 05:58
  • @cularis : I did not get your point, please elaborate – Romi Jun 28 '11 at 05:59
  • 2
    @Romi: I'm sure the query above is different than the one that you might be running. There is nothing wrong with the above query. But as your error message says, it is reading "diamond" as a column which can be if you are using back-ticks (`) instead of single-quotes (') around "diamond" – Abhay Jun 28 '11 at 05:59
  • @Abhay: unfortunately I am very sure thatmy query is same as i have posted. And i am very sure with that i am using only single quote – Romi Jun 28 '11 at 06:04
  • @Abhay: See edit, i included description column values – Romi Jun 28 '11 at 06:10
  • @Romi: right, so what you need is to search the exact work "diamond" in description and the response by ratsbane is the perfect one. But I'm still not sure on the error that you had mentioned - "Unknown column 'diamond' in where clause" – Abhay Jun 28 '11 at 07:43

5 Answers5

9

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

Abdul Rasheed
  • 6,486
  • 4
  • 32
  • 48
ratsbane
  • 890
  • 6
  • 7
3

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

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

See REGEXP.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
0

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

Srinu Babu
  • 422
  • 2
  • 5
  • 16
0
select description from tproduct where description like 'diamond'
gmhk
  • 15,598
  • 27
  • 89
  • 112
-2
select description from tproduct where description = 'diamond'
Mudassir
  • 187
  • 1
  • 9