Syntax error Select all records if it contains specific number in MySQL?

Select all records if it contains specific number in MySQL?

For this, use concat() along with LIKE. Following is the syntax −

select *from yourTableName where concat(',', yourColumnName, ',') like '%,yourValue,%';

Let us create a table −

mysql> create table demo49
−> (
−> id varchar(20)
−> ,
−> first_name varchar(20)
−> );
Query OK, 0 rows affected (1.45 sec)

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

mysql> insert into demo49 values('4,5,6',
−> 'Adam');
Query OK, 1 row affected (0.20 sec)

mysql> insert into demo49 values('5,3,2','Mike');
Query OK, 1 row affected (0.19 sec)

mysql> insert into demo49 values('3,4,9','Bob');
Query OK, 1 row affected (0.14 sec)

Display records from the table using select statement −

mysql> select *from demo49;

This will produce the following output −

+-------+------------+
| id    | first_name |
+-------+------------+
| 4,5,6 | Adam       |
| 5,3,2 | Mike       |
| 3,4,9 | Bob        |
+-------+------------+
3 rows in set (0.00 sec)

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

mysql> select *from demo49 where concat(',', id, ',') like '%,4,%';

This will produce the following output −

+-------+------------+
| id    | first_name |
+-------+------------+
| 4,5,6 | Adam       |
| 3,4,9 | Bob        |
+-------+------------+
2 rows in set (0.00 sec)
Updated on: 2020-11-19T13:15:53+05:30

239 Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements