How to list all tables in MySQL database which match a keyword and ended with year?

Question

How to list all tables in MySQL database which match like "bl_pelanggan"+YEAR?

currently I use the following query:

SHOW TABLES LIKE 'bl_pelanggan%'

but it list all of these:

I want only inside the red box, how to do it?

Answer 1

You can try something like this,

SELECT table_name, table_type, ENGINE
       FROM information_schema.tables
       WHERE table_schema = 'your schema name' AND table_name REGEXP '[[:digit:]]$'AND table_name LIKE 'bl_pelanggan%'
       ORDER BY table_name;