How to Accurately Match Specific Values in Comma-Separated MySQL Strings?

Match specific values ​​in a comma separated MySQL string

When working with tables containing comma-separated values, it is often necessary to isolate specific values ​​within these strings. However, using the LIKE operator with the wildcard character (%) may result in unexpected matches.

Consider the COLORS field in the SHIRTS table, which contains values ​​such as '1,2,5,12,15'. A query like "SELECT * FROM shirts WHERE colors LIKE '%1%'" attempts to find all shirts with a red color (color=1). However, it also unexpectedly retrieves shirts with colors gray (color=12) and orange (color=15).

Solution:

1. Connecting commas:

The classic solution is to concatenate commas on the left and right sides of the string:

<code class="language-sql">SELECT * FROM shirts WHERE CONCAT(',', colors, ',') LIKE '%,1,%'</code>

This will add a comma before and after the string, ensuring that only exact matches (color=1) are selected.

2. Use find_in_set function:

Alternatively, you can use the find_in_set function to search for a specific value in a comma-separated string:

<code class="language-sql">SELECT * FROM shirts WHERE find_in_set('1', colors) > 0</code>

This function will return a non-zero value if the specified value is found in the string, effectively isolating shirts with color=1.

The above is the detailed content of How to Accurately Match Specific Values in Comma-Separated MySQL Strings?. For more information, please follow other related articles on the PHP Chinese website!