Select table names with specific pattern

I created a query to get all table names with "prefix_"

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_NAME LIKE 'prefix_%' AND TABLE_TYPE = 'BASE TABLE'

​x
 
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE 'prefix_%' AND TABLE_TYPE = 'BASE TABLE'​

But in my database, I have tables with prefix_prefix_. How to avoid selecting them?

Answer

You can do this by just adding the exception to the where clause.

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_NAME LIKE 'prefix_%' AND TABLE_NAME NOT LIKE 'prefix_prefix_%' AND TABLE_TYPE = 'BASE TABLE'

 
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE 'prefix_%' AND TABLE_NAME NOT LIKE 'prefix_prefix_%' AND TABLE_TYPE = 'BASE TABLE'​

Advertisement

Answer