Like Operator in Teradata

Database
Enthusiast

Like Operator in Teradata

Hello,

While fetching information from dbc.columns, I am using a filter condition in the where clause - databasename like 'H%DB'. It should retrieve details of all  those databases that starts with H and ends with DB with any number of characters in between. However, the query is returning result for only one database and not for all the databases that starts with H and ends with DB. Any clue what is happening here? or am i missing some basic concepts of Like operator :)

5 REPLIES
Enthusiast

Re: Like Operator in Teradata

Hi, 

You can use the Query like this, 

SEL * FROM DBC.COLUMNS WHERE DATABASENAME LIKE 'H%' AND DATABASENAME LIKE '%DB';

From My understaing, the Problem in the Query is that TD will look for the Column starting with position H followed by any character and then ending with  DB. That is, it will look for values like H1DB,HADB,H6DB,etc.

Thanks & Regards,

Adharssh.
Senior Supporter

Re: Like Operator in Teradata

Hi,

databasename has length 30 so like'H%DB' will only work for databasenames which are actually 30 characters long.

an so use trim(databasename) like 'H%DB'

Ulrich

Junior Supporter

Re: Like Operator in Teradata

Hi.

DBC.Columns.DatabaseName is CHAR(30). Trailing blanks matter!

Cheers.

Carlos.

Junior Contributor

Re: Like Operator in Teradata

Hi Adharssh,

instead of TRIM(DatabasesName) you can also switch to dbc.ColumnsV instead where DatabaseName is a VARCHAR(128).

Since TD12 there's the new set of dbc views all ending on V or VX, the old ones are for backward compatibility only, e.g.

dbc.Columns & dbc.ColumnsX - old, don't use

dbc.ColumnsV & dbc.ColumnsVX - new, recommended

In TD14.10 you must use the new version when you want to implement the new long table names.

Dieter

Enthusiast

Re: Like Operator in Teradata

Hi Dieter,

I didn't post the question. I just replied saying we can use TRIM.

Very valuable information about the dbc.columnsV & dbc.columnsVX. I am actually working in TD 13. When i see tables like dbc.columns & dbc.columnsV.

I thought first one is a Table version and the second one is a View version of the former table. I didn't check the Databasename length and all.

Thanks for the information.