MySQL - Select all records if it contains specific number?

MySQLMySQLi Database

To select all records with specific numbers, use the FIND_IN_SET() in MySQL.

Let us create a table −

Example

mysql> create table demo73
   -> (
   -> interest_id varchar(100),
   -> interest_name varchar(100)
   -> );
Query OK, 0 rows affected (1.48

Insert some records into the table with the help of insert command −

Example

mysql> insert into demo73 values("100,101,103,105","SSC");
Query OK, 1 row affected (0.34

mysql> insert into demo73 values("105,103,1005,1003,104","Computer");
Query OK, 1 row affected (0.10

mysql> insert into demo73 values("110,105,104,111","Novel");
Query OK, 1 row affected (0.31

Display records from the table using select statement −

Example

mysql> select *from demo73;

This will produce the following output −

Output

+-----------------------+---------------+
| interest_id           | interest_name |
+-----------------------+---------------+
| 100,101,103,105       | SSC           |
| 105,103,1005,1003,104 | Computer      |
| 110,105,104,111       | Novel         |
+-----------------------+---------------+
3 rows in set (0.00

Following is the query to select all records if it contains specific number −

Example

mysql> select *from demo73 where find_in_set("103",interest_id) > 0;

This will produce the following output −

Output

+-----------------------+---------------+
| interest_id           | interest_name |
+-----------------------+---------------+
| 100,101,103,105       |          SSC  |
| 105,103,1005,1003,104 |    Computer   |
+-----------------------+---------------+
2 rows in set (0.00 sec)
raja
Published on 11-Dec-2020 05:19:15
Advertisements