Search code examples
mysqlfindtablename

SQL Query to find all table names on a database with MySQL

These are tables list on database MySql version 8.0.17

t_contents_s300_10_2021
t_contents_s300_1_2021
t_contents_s300_2_2021
t_contents_s300_3_2021
t_contents_s34d_1_2021
t_contents_s34g_1_2021
t_contents_s34g_2_2021
t_contents_s3sv_1_2021
t_contents_s3sv_2_2021

I need to find on this list of tables all table like s3 and 1 and 2021

I have tried this query but the return contains all numbers and not only 1

How to resolve this?

Thanks in advance for any help.

mysql> SELECT
    table_name 
FROM
    information_schema.TABLES 
WHERE
    table_name LIKE ( 't_contents_s3%' );
+-------------------------+
| TABLE_NAME              |
+-------------------------+
| t_contents_s300_10_2021 |
| t_contents_s300_1_2021  |
| t_contents_s300_2_2021  |
| t_contents_s300_3_2021  |
| t_contents_s34d_1_2021  |
| t_contents_s34g_1_2021  |
| t_contents_s34g_2_2021  |
| t_contents_s3sv_1_2021  |
| t_contents_s3sv_2_2021  |
+-------------------------+
9 rows in set (0.44 sec)
Solution

  • LIKE is a good direction:

    SELECT  table_name 
FROM  information_schema.TABLES 
WHERE  table_name LIKE  't#_contents#_s3%#_1#_2021' ESCAPE '#';

    or if s3% part has always 4 characters then:

    SELECT  table_name 
FROM  information_schema.TABLES 
WHERE  table_name LIKE  't#_contents#_s3__#_1#_2021' ESCAPE '#';

    ESPACE allows to treat _ like _ and not a single character wildcard.