All Forums UDA
depakjan 11 posts Joined 01/09
25 Jun 2009
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?

lathavim 7 posts Joined 05/09
25 Jun 2009

HiTry using between function in the where clause.Ex. where databasename between 'VDWM0000' and 'VDWM9999'

depakjan 11 posts Joined 01/09
25 Jun 2009

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

Adeel Chaudhry 773 posts Joined 04/08
25 Jun 2009

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

-- If you are stuck at something .... consider it an opportunity to think anew.

BeingHuman 3 posts Joined 06/09
25 Jun 2009

Also check DBC.Dbase ...

Manish
Teradata India

depakjan 11 posts Joined 01/09
26 Jun 2009

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

Jimm 298 posts Joined 09/07
26 Jun 2009

Select Databasename from DBC.TablesWhere 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';

You must sign in to leave a comment.