MySQL query to find a value in a set of values separated by comma in a custom variable

MySQLMySQLi Database

For this, use FIND_IN_SET() in MySQL and use the value from a custom variable. Let us first create a −

mysql> create table DemoTable1411
   -> (
   -> Value int
   -> )
   -> ;
Query OK, 0 rows affected (0.50 sec)

Insert some records in the table using insert −

mysql> insert into DemoTable1411 values(10);
Query OK, 1 row affected (0.11 sec)
mysql> insert into DemoTable1411 values(50);
Query OK, 1 row affected (0.13 sec)
mysql> insert into DemoTable1411 values(60);
Query OK, 1 row affected (0.08 sec)

Display all records from the table using select −

mysql> select * from DemoTable1411;

This will produce the following output −

+-------+
| Value |
+-------+
|    10 |
|    50 |
|    60 |
+-------+
3 rows in set (0.00 sec)

Following is the query to find a value in set of other values. Here, we have set a custom variable first, which is used for the FIND_IN_SET() method −

mysql> set @setOfValues:='70,50,100';
Query OK, 0 rows affected (0.00 sec)
mysql> select * from DemoTable1411
   -> where find_in_set(Value,@setOfValues);

This will produce the following output −

+-------+
| Value |
+-------+
|    50 |
+-------+
1 row in set (0.00 sec)
raja
Published on 12-Nov-2019 05:11:39
Advertisements