CODEWITHSUNDEEP
mysqlmariadb
John
John

Reputation: 13759

Get database name prefix?

Here is one way of getting a list of databases:

SELECT table_schema 'dattabase_name'
FROM information_schema.tables 
GROUP BY table_schema;

However I only need to SELECT a list if they contain a certain prefix. I attempted some combinations of the following without luck:

SUBSTR('database',0,10),
SUBSTR(mysqltable_schema 'database' FROM 0 FOR 10),

I've also tried:

SELECT SCHEMA_NAME, SUBSTR(SCHEMA_NAME, 0, 10) from information_schema.SCHEMATA
SELECT SCHEMA_NAME, SUBSTR(SCHEMA_NAME, FROM 0 FOR 10) from information_schema.SCHEMATA;

How can I get the substr prefix of a database name?

Upvotes: 0

Views: 1759

Answers (2)

Rick James
Rick James

Reputation: 142366

Even shorter:

mysql> SHOW DATABASES LIKE 'tr%';
+----------------+
| Database (tr%) |
+----------------+
| try            |
| try_perm       |
| tryjoin        |
+----------------+
3 rows in set (0.01 sec)

Upvotes: 0

Nick
Nick

Reputation: 147206

String positions in MySQL start at 1, so you need to use SUBSTR(str, 1, 10) to get the first 10 characters. Try this query:

SELECT table_schema as `database`, SUBSTR(table_schema, 1, 10) AS `dbprefix`
FROM `TABLES`
WHERE SUBSTR(table_schema, 1, 10) LIKE '%form%'
GROUP BY table_schema

Upvotes: 2

Related Questions