Table Names SQL

Database
The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.
Highlighted
Fan

Table Names SQL

Hi: I am looking for a SQL to give me the list of tablenames that do not have numbers ( 0 - 9 ) in it. There are many backup tables created on the system ending with a date in number form like Tablename_<numbers>_bkp etc. I want to omit these tables in my result. Please let me know the SQL to write. I tried like this

 

SyntaxEditor Code Snippet

SELECT distinct databasename,tablename,CreatorName,CreateTimeStamp               
,LastAlterName                 
,LastAlterTimeStamp            
FROM DBC.TABLES WHERE 
DATABASENAME IN 
('ABC') and  ( cast(CreateTimeStamp as date) > 1170101 OR cast(LastAlterTimeStamp as date) > 1170101 ) AND TABLENAME NOT LIKE  ALL  ('%BKP%','%OLD%','%BK1%','%1230%')ORDER BY LastAlterTimeStamp ASC;

 

 

Regards,

Naveen.


Accepted Solutions
Junior Contributor

Re: Table Names SQL

The easiest way is probably using a Regular Expression, e.g. 

SELECT *
FROM dbc.TablesV
WHERE RegExp_Similar(tablename , '.*[0-9]$') = 0

to exclude tables names which end on a digit.

 

1 ACCEPTED SOLUTION
4 REPLIES
Senior Apprentice

Re: Table Names SQL

Hi,

Try the following:

SELECT *
FROM dbc.tablesv
WHERE TABLENAME NOT LIKE ANY ('%0%','%1%','%2%','%3%','%4%','%5%','%6%','%7%','%8%','%9%')

You'll need to add in your other criteria but this should handle the 'numbers' bit for you.

Cheers,

Dave

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Junior Contributor

Re: Table Names SQL

The easiest way is probably using a Regular Expression, e.g. 

SELECT *
FROM dbc.TablesV
WHERE RegExp_Similar(tablename , '.*[0-9]$') = 0

to exclude tables names which end on a digit.

 

Fan

Re: Table Names SQL

Thanks Deiter. I used the condition as 

SyntaxEditor Code Snippet

 ((RegExp_Similar(tablename , '.*[0-9]$') <> 0) OR (TABLENAME NOT LIKE  ALL  ('%BKP%','%OLD%','%BK1%')))
Junior Contributor

Re: Table Names SQL

You can probably include the LIKE logic in the RexEx:

RegExp_Similar(tablename , '(.*[0-9]$|.*(BKP|OLD|BK1).*)', 'i') = 0

Exclude all tablename with digits at the end or containing BKP or OLD or BK1