Home >Database >Mysql Tutorial >How to Check if Numbers Exist in a Comma-Separated List in MySQL?

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

Linda Hamilton
Linda HamiltonOriginal
2024-11-16 06:28:03944browse

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!

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn