Case Sensitivity in Teradata

Case sensitivity of columns:

Tables can be created in ANSI mode or Teradata mode.

• We know that Teradata mode is case insensitive , so the character columns by default are defined as NOT CASESPECIFIC.

The data is stored in the case entered, but while making comparisons case is ignored.

.ie 'abc' is same as 'ABC'

• We know that ANSI mode is case sensitive, so the character columns by default are defined as CASE SPECIFIC.

The data will be stored and retrieved in same case entered. However comparisons are case sensitive.

.ie 'ABC' is not same as 'abc'

• We can set the session transaction using

SET SESSION TRANSACTION ANSI;

Or

SET SESSION TRANSACTION BTET;

• If we are in ANSI mode and wish to make case insensitive comparisons we can use the functions UPPER and LOWER to both side of the comparisons.

Ex:

Select * from employee where UPPER(employee_name)=UPPER(manager_name);

OR

Select * from employee where LOWER(employee_name)=LOWER(manager_name);

• When in ANSI mode we can enforce no case specific comparisons by making use of 'NOT CASESPECIFIC' attribute to the column in parenthesis following the column name.

SELECT * from employee where employee_name(NOT CASESPECIFIC)=manager_name(NOT CASESPECIFIC);

We can also abbreviate it as 'NOT CS'

SELECT * from employee where employee_name(NOT CS)=manager_name(NOT CS);

• When in BTET mode we can enforce case specific comparisons by making use of 'CASESPECIFIC' attribute to the column in  parenthesis following the column name.

SELECT * from employee where employee_name(CASESPECIFIC)=manager_name(CASESPECIFIC);

We can also abbreviate it as 'CS'