MySQL REGEXP to fetch string + number records beginning with specific numbers?

For this, use REGEXP and fetch records beginning with specific numbers. Following is the syntax:

Select yourColumnName1,yourColumnName2
from yourTableName
where yourColumnName2 REGEXP '^yourStringValue[yourNumericValue]';

Let us create a table −

mysql> create table demo45
-> (
−> id int not null auto_increment primary key,
−> value varchar(50)
−> );
Query OK, 0 rows affected (1.50 sec)

Insert some records into the table with the help of insert command. We are inserting records mixed with strings and numbers i.e. “John500, “John6500”, etc −

mysql> insert into demo45(value) values('John500');
Query OK, 1 row affected (0.12 sec)
mysql> insert into demo45(value) values('John1500');
Query OK, 1 row affected (0.11 sec)
mysql> insert into demo45(value) values('John5500');
Query OK, 1 row affected (0.42 sec)
mysql> insert into demo45(value) values('John6500');
Query OK, 1 row affected (0.10 sec)
mysql> insert into demo45(value) values('John8600');
Query OK, 1 row affected (0.19 sec)

Display records from the table using select statement −

mysql> select *from demo45;

This will produce the following output −

+----+----------+
| id | value    |
+----+----------+
|  1 | John500  |
|  2 | John1500 |
|  3 | John5500 |
|  4 | John6500 |
|  5 | John8600 |
+----+----------+
5 rows in set (0.00 sec)

Following is the query to fetch records with specific numbers i.e. 5 and 6 here −

mysql> select id,value
−> from demo45
−> where value REGEXP '^John[56]';

This will produce the following output −

+----+----------+
| id | value    |
+----+----------+
|  1 | John500  |
|  3 | John5500 |
|  4 | John6500 |
+----+----------+
3 rows in set (0.00 sec)