How to use LIKE in queries

Database

How to use LIKE in queries

Hi,

I am new to Teradata and having difficulty in coming up with a query to retrieve certain data.

Here is my query.

select 'drop table ' || trim(databasename) || '.' || trim (tablename) || ';'
from dbc.tables
where databasename = 'ITNWH'
and tablekind='T'
and tablename like '%\_L' ESCAPE '\';

I am trying to build this script for all tables in a database that end with "_L" in table name. But in my case it is NOT showing all tables in the output.

I see only the following one line in the output, where as I was expecting the query to return 49 rows.

drop table ITNWH.MEMBER_MEMBERCOMPANYLOCATION_L;

Look at some of the data I have.

select 'drop table ' || trim(databasename) || '.' || trim (tablename) || ';'
from dbc.tables
where databasename = 'ITNWH'
and tablekind='T';

drop table ITNWH.MEMBER_MEMBERCOMPANYLOCATION_L;
drop table ITNWH.MASTERPURCHASEORDER;
drop table ITNWH.USDA_ITEMGROUPS_L;
drop table ITNWH.MASTERPO_CATALOGENTRY;
drop table ITNWH.USDA_PRODUCTS_L;
drop table ITNWH.PURCHASEORDER2_CATALOGENTRY;
drop table ITNWH.USDA_DAILY_L;
drop table ITNWH.USDA_PRODUCTS;
drop table ITNWH.CARMA_PC_SPLIT;
drop table ITNWH.LINEITEMS_EXTRASERVICES_L;
drop table ITNWH.PURCHASEORDER2;
drop table ITNWH.POS_BUCKET;
drop table ITNWH.POS_PRODUCT;
drop table ITNWH.POS_PRODUCT_HP_L;
drop table ITNWH.POS_ITEM_PRICE_MGT_L;
drop table ITNWH.POS_PRODUCT_H_L;
drop table ITNWH.POS_REPORT_DETAIL;
drop table ITNWH.MEMBERCOMPANYLOCATION_L;
drop table ITNWH.POS_PRODUCT_CVV_L;
drop table ITNWH.CARMA_POS_INVOICE_LINK_L;
drop table ITNWH.INVOICE_CATALOGENTRY_L;
drop table ITNWH.CARMA_POC_REBATE_INV_L;
drop table ITNWH.PURCHASEORDER2_TOTALS;
drop table ITNWH.POS_BUCKET_L;
drop table ITNWH.DEFAULTPRIVATEPARTIES_L;
drop table ITNWH.MEMBERCOMPANY_FLAGS_L;
drop table ITNWH.POS_PRODUCT_CV;
drop table ITNWH.CARMA_CONTRACT;
drop table ITNWH.POS_UNIT_HIERACHY;
drop table ITNWH.CUST_DATES_CONTRACT_INV_L;
drop table ITNWH.MEMBERCOMPANYCATALOG;
drop table ITNWH.CATEGORY_CONTAINER;
drop table ITNWH.F_TIME;
drop table ITNWH.PURCHASEORDER2_CATENT_SIZES;
drop table ITNWH.CUST_DATES_CONTRACT_INV;
drop table ITNWH.CARMA_PC_SPLIT_L;
---
---
---

Can anyone help me in fixing this query? I don't understand why it is not working.

Thanks
1 REPLY
Senior Apprentice

Re: How to use LIKE in queries

Tablename is a CHAR(30) and any comparison using LIKE does *not* append trailing blanks like "=" etc.

Two possible solutions come to mind:
- TRIM(tablename) like '%\_L' ESCAPE '\';
- when you're on TD12+ you should use dbc.tablesV, which returns the tablename as a Varchar(128)

Dieter