"Show databases" with condition

i would like to query a MySql Database to show me all the existing databases based on provided condition (condit开发者_StackOverflow社区ion applied on the database name). now since my condition is complex, the simple "LIKE" condition is not enough, and i need to use the regular WHERE clause.

can anyone provide a sample of how to do that?

USE INFORMATION_SCHEMA;
SELECT `SCHEMA_NAME` from `SCHEMATA` WHERE `SCHEMA_NAME` LIKE "%whatever%";

Read more in the docs.

There is also another, in my eyes more convenient command:

SHOW DATABASES WHERE `Database` LIKE '%whatever1%' OR `Database` LIKE '%whatever2%';

With the WHERE version you can run more flexible queries than with the very limited LIKE version.

It is important to put Database under the backquotes because it is a keyword in MySQL.

See also the MySQL documentation and the Extensions to SHOW Statements.

The solution given by gnud can be more optimized to:

SELECT `schema_name` from INFORMATION_SCHEMA.SCHEMATA WHERE `SCHEMA_NAME` LIKE "%whatever%";

Task completed with only one sql query.

When the 'USE' SQL command is used, then all the database information is read prior to selecting it, like the list of tables and it's content. Using the above query, you will be in your own database and able to query content from a table in different database.

You could use:

USE INFORMATION_SCHEMA -A;

This avoids reading of database contents.

Where:

schema_name --> column name INFORMATION_SCHEMA.SCHEMATA --> name before '.' is database name and after it is tables name.

Hope this solution helps.

You can use the show databases statement as follow: SHOW {DATABASES | SCHEMAS}[LIKE 'pattern' | WHERE expr] i.e, you can just write show databases like '%dbname%'; or show databases wheredatabaselike '%dbname%';