How to Query for Exact Matches in Comma-Separated MySQL Strings?

Query for exact matches of comma separated strings in MySQL

Querying for specific values ​​often presents challenges when working with MySQL fields that contain comma-separated strings. For example, when you query for all records with a specific value in the string, you might inadvertently retrieve records that contain other values ​​for the target number.

Consider the following query:

<code class="language-sql">select * from shirts where colors like '%1%'</code>

This query is designed to retrieve all shirts with the color red (color=1). However, it also retrieves shirts with colors gray (=12) and orange (=15) because these colors contain the number 1.

To solve this problem, you can improve the query to ensure that it only selects the exact color value you are searching for. The following are two commonly used methods:

1. Connect commas

This method involves adding commas to the left and right of the target value. Doing this ensures that the search string only matches those values ​​that are exactly equal to the desired value.

<code class="language-sql">select * from shirts where CONCAT(',', colors, ',') like '%,1,%'</code>

2. Use find_in_set

find_in_set functions can also be used to achieve desired results. This function takes two parameters:

• The target value you are searching for
• The comma separated string you want to search in

When find_in_set returns a value greater than 0, it means that the target value exists in the string. Here is a sample query:

<code class="language-sql">select * from shirts where find_in_set('1', colors) > 0</code>

By using either of these two methods, you can ensure that your MySQL queries accurately retrieve records for the exact color values ​​you are interested in, without picking up unwanted matches.

The above is the detailed content of How to Query for Exact Matches in Comma-Separated MySQL Strings?. For more information, please follow other related articles on the PHP Chinese website!