How to check CASESPECIFIC in Teradata

We all know that Teradata by default is case insensitive.Even the column definitions while creating a table is NOT CASESPECIFIC by default.However if the requirement is to create a column as casespecific then what is the efficient way of using such columns in filtering(WHERE clause)? You can create a CASESPECIFIC column by explicitly mentioning that column as CASESPECIFIC in its DDL.However if you are using that column in WHERE claus it may result in undesirable results.You should be careful while using CASESPECIFIC columns.Say if in a table with two columns, NAME(CASESPECIFIC) and CITY I am having an entry as
Nitin    BANGALORE
If I will try below Query I will not get any result :

SELECT NAME,CITY FROM TABLENAME WHERE NAME='NITIN';

To get the result we can do something like this :

SELECT NAME,CITY FROM TABLENAME WHERE UPPER(NAME)='NITIN';

There are 2 modes in which Teradata works. Default is TERA mode which is case insensitive. However if you explicitly set ANSI mode then teradata becomes case sensitive.

 To switch to ANSI mode run below command before logon :

.SET SESSION TRANSACTION ANSI

To revert to TERA mode use below query before logon:

.SET SESSION TRANSACTION BTET

Leave a Reply

Your email address will not be published. Required fields are marked *