Get table names from a database conditionally?-CodePudding

I have a database that consists of over 50000 tables. Most of the tables except for few end with hex values. So they look something like :-

table1_dft5678f_6e54_de54_7c54_5fd3b54cbc43
table2_dft5674f_6e54_cb54_7c55_5fd3b54ebcca
table3_dft5673f_6e54_2d54_7c53_fd23b54cbf43

and so on. And there are certain tables that don't end up with hex values.

table_name1
table_name2

How can i find the name of the tables that don't end up with hex values?

CodePudding user response：

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE ...condition...

I'm not sure how you want to form the condition. Perhaps LENGTH(TABLE_NAME) < 43? Or TABLE_NAME REGEXP '^table_name[0-9]$'? It depends on what your real table names look like. I assume the ones you show in your question above are just artificial examples.