SQL select like - containing only 1 word

Question

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

Answer 1

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

Answer 2

Try to use

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

Answer 3

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.

Answer 4

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

select *
from gallery_images
where image_colors = 'green'