16.20 - Example: Using a Table Definition with CASESPECIFIC Attributes - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Functions, Expressions, and Predicates

prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
created_date
March 2019
category
Programming Reference
featnum
B035-1145-162K

Consider the following table definition where the character columns have CASESPECIFIC attributes:

   CREATE TABLE employee
     (last_name CHAR(32) CASESPECIFIC
     ,city      CHAR(32) CASESPECIFIC
     ,emp_id    CHAR(9)  CASESPECIFIC
     ,emp_ssn   CHAR(9)  CASESPECIFIC);

To compare on a case blind basis:

   SELECT emp_id
   FROM employee
   WHERE UPPER(emp_id) = UPPER(emp_ssn);

To compare with a string literal:

   SELECT emp_id
   FROM employee
   WHERE UPPER(city) = 'MINNEAPOLIS';

Teradata SQL also has the data type attribute NOT CASESPECIFIC, which allows case blind comparisons. Note that the data type attributes CASESPECIFIC and NOT CASESPECIFIC are Teradata extensions to the ANSI standard.