Matching table names with show tables

Question

Can someone help me with this mysql problem? I am trying to get all table names from db.

In my db, I have tables like:

_table1, _Table 2, Table3, Table 4, table5_xrefs

but I want to get only tables without _ at the beginning and without _xref at the end. Therefore, in this example, I only need tables3 and table4.

I use show tablesto show all table names and after I use php to match the correct table name. I was wondering if I can do the same using only mysql query.

Answer 1

, , , SHOW TABLES query, tables_in_ . , : test:

SHOW TABLES 
      WHERE tables_in_test NOT LIKE '\_%' 
        AND tables_in_test NOT LIKE '%\_xrefs'

information_schema :

SELECT TABLE_NAME 
  FROM information_schema.TABLES
 WHERE TABLE_SCHEMA = SCHEMA() /* = 'test'*/
   AND TABLE_NAME NOT LIKE '\_%'
   AND TABLE_NAME NOT LIKE '%\_xrefs'

Answer 2

LIKE WHERE SHOW TABLES.