MySQL Regex

MySQL Regex

We find requirements where we are looking for only certain things from the entire output we have. For instance we have a large numbers of tables or databases listed and we are looking for the ones with certain string / regex.

I have seen many people use the mysql CLI in Linux to pass the MySQL query and use grep to filter the results. But MySQL has the ability to filter the results for us.

There are option of using LIKE, however it has it’s own limitation. The other option is to use REGEXP. This is a powerful option you can use for filtering the results.

Let’s take a few examples. I will concentrate more on the SHOW statements for MYSQL though. These examples may fall under the purview of Extensions to SHOW statements.

MySQL Regex

I have a bunch of databases, and I need to see the databases containing gagan.

Let’s see what all databases I have:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| gagan              | 
| gagan_1234         |
| gagan_5678         | 
| gagan_9012         | 
| gagan_new          | 
| information_schema | 
| mysql              | 
| original_gagan     | 
+--------------------+
8 rows in set (0.00 sec)

The following is the Syntax that will be used to SHOW databases containing ‘gagan’.

First, the simple way using LIKE

show databases LIKE ‘%STRING%’;

Statement:

show databases LIKE ‘%gagan%’;

Second, using REGEXP

show databases WHERE `Database` REGEXP ‘STRING’;

Statement:

show databases WHERE `Database` REGEXP ‘gagan’;

The following will be the output in both the cases:

+----------------+
| Database       |
+----------------+
| gagan          |
| gagan_1234     |
| gagan_5678     | 
| gagan_9012     | 
| gagan_new      | 
| original_gagan |
+----------------+
6 rows in set (0.00 sec)

MySQL Regex

Now, let’s take some complex examples. Fetech the databases with ‘_xxxx’ in them. In this case we have numbers and we will fetch the databases with numbers after the character ‘_’.

The statement to be used:

show databases WHERE `Database` REGEXP ‘gagan_[0-9]’;

Output:

+------------+
| Database   |
+------------+
| gagan_1234 | 
| gagan_5678 | 
| gagan_9012 | 
+------------+
3 rows in set (0.00 sec)

MySQL Regex – Databases Starting with

Databases starting with ‘gagan’.

Statement:

show databases WHERE `Database` REGEXP ‘^gagan’;

Output:

+------------+
| Database   |
+------------+
| gagan      | 
| gagan_1234 | 
| gagan_5678 | 
| gagan_9012 | 
| gagan_new  | 
+------------+
5 rows in set (0.00 sec)

MySQL Regex – Multiple conditions

Database starting and ending with ‘gagan’ along with databases ‘gagan_[0-9]’.

Statement:

show databases WHERE `Database` REGEXP ‘^gagan$\|gagan_[0-9]’;

Output:

+------------+
| Database   |
+------------+
| gagan      | 
| gagan_1234 | 
| gagan_5678 | 
| gagan_9012 | 
+------------+
4 rows in set (0.00 sec)

There are many more that I can add, but this should give you an idea to get things as per your requirement.

MySQL Regex

No Comments

Post a Comment

Time limit is exhausted. Please reload CAPTCHA.