Find values in a comma separated string in a MySQL query

0 votes
Suppose, I have a field COLORS (varchar(50)) in my table SHIRTS that contains a comma-delimited string such as 1,2,5,12,15,. where each number represents the available colors.

How should I write the query so that it selects ONLY the color 1 and not all colors containing the number 1?
Sep 10, 2018 in Database by DataKing99
• 8,130 points
1,875 views

1 answer to this question.

0 votes

You can add commas to the left and right:

select * from shirts where CONCAT(',', colors, ',') like '%,1,%'

But you can also try out the below option:

select * from shirts where find_in_set('1',colors) <> 0
answered Sep 10, 2018 by darklord
• 6,140 points

Related Questions In Database

0 votes
1 answer

How to display the queries executed by a particular user in MySQL?

From the version 5.1.7 onward, MySQL allows ...READ MORE

answered Mar 7 in Database by Mishti
• 480 points
37 views
0 votes
1 answer

Types of tables in MySQL

MyISAM is the default table that is ...READ MORE

answered Aug 21, 2018 in Database by CodingByHeart77
• 3,680 points
88 views
0 votes
1 answer

TRIGGERS used in MySQL tables

The following TRIGGERS are allowed in MySQL:  BEFORE ...READ MORE

answered Aug 21, 2018 in Database by CodingByHeart77
• 3,680 points
24 views
0 votes
1 answer

Reset a sequence in Oracle

You can try out something like this create ...READ MORE

answered Sep 24, 2018 in Database by DataKing99
• 8,130 points
283 views
0 votes
1 answer

Why is not preferable to use mysql_* functions in PHP?

The reasons are as follows: The MySQL extension: Does ...READ MORE

answered Sep 7, 2018 in Database by DataKing99
• 8,130 points
42 views
0 votes
1 answer
0 votes
1 answer

How to connect to MySQL Database?

using MySql.Data; using MySql.Data.MySqlClient; namespace Data { ...READ MORE

answered Oct 12, 2018 in Database by Frankie
• 9,810 points
89 views
0 votes
1 answer

Fetch first 5 characters of the string

There are a lot of ways to ...READ MORE

answered Oct 22, 2018 in Database by DataKing99
• 8,130 points
30 views
0 votes
1 answer

How to do a batch insert in MySQL

You can try out the following query: INSERT ...READ MORE

answered Sep 10, 2018 in Database by darklord
• 6,140 points
233 views
0 votes
1 answer

Write a SQL query to find the names of employees that begin with ‘S’

To display the name of the employees ...READ MORE

answered Sep 26, 2018 in Database by darklord
• 6,140 points
44 views