Syntax error How to fetch all databases with name having upper case character after some word using MySQL?

How to fetch all databases with name having upper case character after some word using MySQL?

For this, use regular expression. The syntax is as follows −

select * from information_schema.schemata WHERE SCHEMA_NAME REGEXP '^yourValue_+[A-Z]';

Let us create some databases −

mysql> create database bank_APP1;
Query OK, 1 row affected (0.00 sec)
mysql> create database bank_APP2;
Query OK, 1 row affected (0.00 sec)
mysql> create database bank_APP3;
Query OK, 1 row affected (0.00 sec)

Here is the query to get all databases having upper case character after some word −

mysql> select * from information_schema.schemata
   WHERE SCHEMA_NAME REGEXP '^bank_+[A-Z]';

This will produce the following output −

+--------------+-------------+----------------------------+------------------------+----------+
| CATALOG_NAME | SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH |
+--------------+-------------+----------------------------+------------------------+----------+
| def          | bank_app1   | utf8                       | utf8_unicode_ci        | NULL     |
| def          | bank_app2   | utf8                       | utf8_unicode_ci        | NULL     |
| def          | bank_app3   | utf8                       | utf8_unicode_ci        | NULL     |
+--------------+-------------+----------------------------+------------------------+----------+
3 rows in set (0.00 sec)
Updated on: 2019-12-31T06:29:10+05:30

100 Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements