REGEX Match integers 6 through 10 in MySQL?

MySQLMySQLi Database

Here you can use BETWEEN operator. The syntax is as follows −

SELECT *FROM yourTableName WHERE yourColumnName BETWEEN 6 AND 10;

You can use regular expression like this. The syntax is as follows −

SELECT *FROM yourTableName WHERE yourColumnName REGEXP '10|[6-9]';

To understand the both syntax, let us create a table. The query to create a table is as follows −

mysql> create table RegularExpressionDemo
   -> (
   -> Id int
   -> );
Query OK, 0 rows affected (1.11 sec)

Now you can insert some records in the table using insert command. The query is as follows −

mysql> insert into RegularExpressionDemo values(1);
Query OK, 1 row affected (0.16 sec)
mysql> insert into RegularExpressionDemo values(2);
Query OK, 1 row affected (0.13 sec)
mysql> insert into RegularExpressionDemo values(3);
Query OK, 1 row affected (0.17 sec)
mysql> insert into RegularExpressionDemo values(4);
Query OK, 1 row affected (0.14 sec)
mysql> insert into RegularExpressionDemo values(5);
Query OK, 1 row affected (0.10 sec)
mysql> insert into RegularExpressionDemo values(6);
Query OK, 1 row affected (0.24 sec)
mysql> insert into RegularExpressionDemo values(7);
Query OK, 1 row affected (0.10 sec)
mysql> insert into RegularExpressionDemo values(8);
Query OK, 1 row affected (0.08 sec)
mysql> insert into RegularExpressionDemo values(9);
Query OK, 1 row affected (0.22 sec)
mysql> insert into RegularExpressionDemo values(10);
Query OK, 1 row affected (0.23 sec)

Display all records from the table using select statement. The query is as follows −

mysql> select *from RegularExpressionDemo;

The following is the output −

+------+
| Id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|    6 |
|    7 |
|    8 |
|    9 |
|   10 |
+------+
10 rows in set (0.00 sec)

Here is the query to match integer 6 through 10.

Case 1 − Use the BETWEEN operator. The query is as follows −

mysql> select *from RegularExpressionDemo where Id between 6 and 10;

The following is the output −

+------+
| Id   |
+------+
|    6 |
|    7 |
|    8 |
|    9 |
|   10 |
+------+
5 rows in set (0.00 sec)

Here is the query that use REGEXP to match integer 6 through 10 −

mysql> select *from RegularExpressionDemo where Id REGEXP '10|[6-9]';

The following is the output −

+------+
| Id   |
+------+
|    6 |
|    7 |
|    8 |
|    9 |
|   10 |
+------+
5 rows in set (0.01 sec)
raja
Published on 26-Feb-2019 12:29:27
Advertisements