Seaching for particular databases

UDA

Seaching for particular databases

We have this table dbc.columns which lists down all databases and the tables in our region... in this i have database series VDWM0908, VDWM0907, VDWM0906 and SO ON, the last 4 digits will be of any number we dunno the starting or the ending value of these 4 digits... if so i need to query this table dbc.columns and get the resultset having only these VDWM0907 tables(the last 4 digits should be numbers)... i should not be getting VDWM_abd, VDWMaadd ,,,, so what kind of searching can i use.. is there any regular expressions support in teradata?
6 REPLIES

Re: Seaching for particular databases

Hi

Try using between function in the where clause.

Ex. where databasename between 'VDWM0000' and 'VDWM9999'

Re: Seaching for particular databases

This does'nt work it returns resluts like VDWM0701_TRNSPT_MRG and so on..... :( ....
Teradata Employee

Re: Seaching for particular databases

Hello,

Couple points to share, one DBC.Columns contains all the Columns information system-wide. Second, how about adding the limit on DatabaseName's length to 8?

HTH!

Regards,

Adeel

Re: Seaching for particular databases

Also check DBC.Dbase ...

Re: Seaching for particular databases

Thanks guys.. but how do you check for length in where condition .. is there any .length() function or something in teradata?

N/A

Re: Seaching for particular databases

Select Databasename
from DBC.Tables
Where Chars(Trim(Databasename)) = 8
And Substr(Databasename,5,1) Between '0' And '9'
And Substr(Databasename,6,1) Between '0' And '9'
And Substr(Databasename,7,1) Between '0' And '9'
And Substr(Databasename,8,1) Between '0' And '9'
;