Solved

filter out mySQL table name

Posted on 2014-10-08
3
406 Views
Last Modified: 2014-12-15
Dear all,

right now try to filter out table name with prefxi tblT_ so we don't want this kind of table from showing out, my query is :

 SELECT  DISTINCT TABLE_NAME 	
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_SCHEMA=<databaseName>  and table_type<> 'view' and TABLE_NAME  NOT LIKE 'tblT_%';

Open in new window


and I try to test the result set by only show out table has prefix like that:

 SELECT  DISTINCT TABLE_NAME 	
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_SCHEMA=<databaseName>  and table_type<> 'view' and TABLE_NAME  LIKE 'tblT_%';

Open in new window


but it seems MySQL will return all table with name prefix with tblT% instead of what we want : tblT_xxxxxx

how to solve this ?
0
Comment
Question by:marrowyung
  • 2
3 Comments
 
LVL 15

Accepted Solution

by:
Haris Djulic earned 500 total points
ID: 40370077
You are using the  wildcard chareacter in your query so you need to 'tell' My SQL engine that you want it in the name i.e.

use it like this

'
SELECT  DISTINCT TABLE_NAME 	
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_SCHEMA=<databaseName>  and table_type<> 'view' and TABLE_NAME  LIKE 'tblT\_%';

Open in new window

0
 
LVL 1

Author Closing Comment

by:marrowyung
ID: 40370156
very nice !
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40499817
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
Creating and Managing Databases with phpMyAdmin in cPanel.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now