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
3,172 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,290 points

Related Questions In Database

0 votes
1 answer

MySQL query finding values in a comma separated string

To find MySQL query values in a comma-separated ...READ MORE

answered Oct 4 in Database by Daric
• 500 points
51 views
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
52 views
0 votes
1 answer

showing an error in a MySQL trigger

use this code: CREATE TRIGGER sample_trigger_msg ...READ MORE

answered Sep 27 in Database by Omaiz
• 560 points
30 views
0 votes
1 answer

Query to list number of records in each table in a database

Use the below code to list number of ...READ MORE

answered Oct 11 in Database by Omaiz
• 560 points
48 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
67 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
127 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
123 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,290 points
466 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,290 points
59 views