CODEWITHSUNDEEP
sqlselect
labu77
labu77

Reputation: 797

SQL select like - containing only 1 word

A SQL Table has a field with the name:

 image_colors

The value of this field can be a row of different colors - example:

green red blue white

When I search for a specific color, I use:

SELECT * 
FROM  `gallery_images` 
WHERE  `image_colors` LIKE  '%green%'

In this case, the value:

green red blue white

contains green and will be selected.

Question:

Is it possible with SQL to find only values with only 1 Word:

green

Upvotes: 0

Views: 1525

Answers (4)

maulik kansara
maulik kansara

Reputation: 1107

Do not use Like operator when you want records with only 'green' color.

select *
from gallery_images
where image_colors = 'green'

Upvotes: 0

JPatel
JPatel

Reputation: 103

Your existing query will work for single word also. No need to do any change for single word value in search field.

But of course this query will degrade performance of your application.

Instead of storing multiple values in single column, it is better to create another table to store colors in form of Integer value as multiple rows.

Upvotes: 0

Christian
Christian

Reputation: 827

Try to use

SELECT * 
FROM  gallery_images 
WHERE  trim(image_colors)= 'green'

Upvotes: 0

Kamil Gosciminski
Kamil Gosciminski

Reputation: 17147

Yes, use simple equality comparison to select only values matching green:

select *
from gallery_images
where image_colors = 'green'

Notes:

  • backticks are not necessary in your case and should be avoided when not needed
  • you should probably change your data model for many-to-many with colors dictionary table and junction table between gallery_images and colors to normalize your data and make lookups faster

Upvotes: 1

Related Questions