CODEWITHSUNDEEP
sql
degath
degath

Reputation: 1621

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'

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

Upvotes: 2

Views: 2445

Answers (2)

Zulatin
Zulatin

Reputation: 283

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'

Upvotes: 5

Fahmi
Fahmi

Reputation: 37473

You can try using subquery and not like 

   select * from 
   (
     SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES 
     WHERE TABLE_NAME LIKE 'prefix_%' and AND TABLE_TYPE = 'BASE TABLE'
   )A where ABLE_NAME LIKE 'prefix_prefix_%'

Upvotes: 2

Related Questions