By degath


2019-02-11 09:18:55 8 Comments

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?

2 comments

@Zulatin 2019-02-11 09:21:07

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'

@degath 2019-02-11 09:27:56

great answer, I'll answer it asap. Can I invite you to the chat. I have another question if you have time I would've be greatful chat.stackoverflow.com/rooms/11391/sql

@fa06 2019-02-11 09:21:07

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_%'

Related Questions

Sponsored Content

25 Answered Questions

[SOLVED] Finding duplicate values in a SQL table

  • 2010-04-07 18:17:29
  • Alex
  • 2250922 View
  • 1529 Score
  • 25 Answer
  • Tags:   sql duplicates

20 Answered Questions

[SOLVED] SQL update from one Table to another based on a ID match

40 Answered Questions

[SOLVED] How to import an SQL file using the command line in MySQL?

30 Answered Questions

[SOLVED] SQL select only rows with max value on a column

32 Answered Questions

[SOLVED] How do I UPDATE from a SELECT in SQL Server?

10 Answered Questions

[SOLVED] Can I concatenate multiple MySQL rows into one field?

17 Answered Questions

[SOLVED] How can I get column names from a table in SQL Server?

25 Answered Questions

19 Answered Questions

[SOLVED] Get list of all tables in Oracle?

  • 2008-10-15 17:54:49
  • vitule
  • 2006076 View
  • 1002 Score
  • 19 Answer
  • Tags:   sql oracle

41 Answered Questions

[SOLVED] Table Naming Dilemma: Singular vs. Plural Names

Sponsored Content