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

Retrieving Data When Values Exist in a Comma-Separated List (MySQL)

Problem:

You have a MySQL table where one of the columns, called NUMBERS, contains comma-separated lists of numbers. You want to determine whether specific numbers are present in the NUMBERS column for particular rows. For example, you need to check if the numbers 3 and 15 are present in the NUMBERS column for a row with the UID of 2.

Solution:

You can use the IN operator to perform this check. The IN operator allows you to specify a list of values and check if a given value matches any of the values in the list. In this case, you can use the IN operator to check if the numbers 3 and 15 are present in the NUMBERS column for a specific row.

One way to do this is to use the following query:

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

This query will return all rows from the table where the numbers 3 and 15 are both present in the NUMBERS column. In your example, this query would return the row with the UID of 2 because it contains both 3 and 15 in the NUMBERS column.

Another alternative is to use the FIND_IN_SET() function. The FIND_IN_SET() function takes two arguments: a value and a comma-separated list. It returns the position of the value in the list if it is present, or 0 if the value is not present. You can use this function to check if a specific number is present in the NUMBERS column by using the following query:

SELECT * FROM table WHERE FIND_IN_SET(3, NUMBERS) > 0 AND FIND_IN_SET(15, NUMBERS) > 0;

This query will return the same result as the previous query, but it uses the FIND_IN_SET() function to determine whether the numbers 3 and 15 are present in the NUMBERS column.

Both of these methods can be used to check if specific numbers are present in a comma-separated list in a MySQL table. You can use whichever method you prefer.

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