CODEWITHSUNDEEP
mysqlsql
handsome
handsome

Reputation: 2422

precision using SQL LIKE

Hello everyone i want to filter db records using LIKE but i need more accurate results Im using MySQL: 5.1.61-log

products

and a few records (updated, just added a url to illustrate)

1 | prod1 | https://www.site.com/?q=yellow whiteBLUEred
2 | prod2 | https://www.site.com/?q=yellow-and-green blue orange_black

SELECT * FROM products WHERE keyword LIKE '%yellow-and-green%'

returns (1) row. and this is correct

but

SELECT * FROM products WHERE keyword LIKE '%yellow%'

returns (2) rows but only one record have the keyword "yellow"

how can i fix this? the LIKE operator is the right for this situation?

thank you!

Upvotes: 0

Views: 979

Answers (2)

WarrenT
WarrenT

Reputation: 4542

How about:

where keywords = 'yellow'
   or keywords like 'yellow %'
   or keywords like '% yellow'
   or keywords like  % yellow %'

This solves the problem where yellow might (or might not) be the first and/or the last "word" in the list.

Upvotes: 1

Andrew
Andrew

Reputation: 2335

why not use:

SELECT * FROM products WHERE keyword LIKE '%yellow %'

If you've always got a space then include it in your query

To deal with other possibilities:

SELECT * FROM products WHERE (keyword LIKE 'yellow %' or keyword LIKE '% yellow %' or keyword LIKE '% yellow')

Upvotes: 1

Related Questions