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,100 points
542 views

1 answer to this question.

Your answer

Your name to display (optional):
Privacy: Your email address will only be used for sending these notifications.
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
• 440 points
18 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
29 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
15 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,100 points
75 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,100 points
23 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,570 points
51 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,100 points
19 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
73 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
25 views

© 2018 Brain4ce Education Solutions Pvt. Ltd. All rights Reserved.
"PMP®","PMI®", "PMI-ACP®" and "PMBOK®" are registered marks of the Project Management Institute, Inc. MongoDB®, Mongo and the leaf logo are the registered trademarks of MongoDB, Inc.