How to Check if Numbers Exist in a Comma-Separated List in MySQL?

Using IN to Query for Numbers in Comma-Separated Lists

In MySQL, determining if a set of numbers is present within a comma-separated list stored in a BLOB field can be achieved using the IN operator. This approach avoids using LIKE %%, which is not recommended for such scenarios.

Consider the following table:

UID | NUMBERS
------|--------------------
1 | 1,13,15,20
2 | 3,10,15,20
3 | 3,15

To check if both 3 and 15 exist in the NUMBERS field, use the following query:

SELECT * FROM table WHERE 3 IN (NUMBERS) AND 15 IN (NUMBERS)

The IN operator effectively searches for the specified values within the comma-separated string stored in the NUMBERS field. For instance:

WHERE 3 IN (2,3,6,8,90)

This query checks if the number 3 is present in the list (2,3,6,8,90).

Note that this approach is also applicable to non-numeric values stored in comma-separated strings.

The above is the detailed content of How to Check if Numbers Exist in a Comma-Separated List in MySQL?. For more information, please follow other related articles on the PHP Chinese website!