Case Sensitivity - Advanced SQL Engine - Teradata Database

SQL Functions, Expressions, and Predicates

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-28
dita:mapPath
SQL_Functions__Expressions__and_Predicates.Upload_071421/djk1612415574830.ditamap
dita:ditavalPath
SQL_Functions__Expressions__and_Predicates.Upload_071421/wrg1590696035526.ditaval
dita:id
B035-1145
lifecycle
previous
Product Category
Teradata Vantage™

All character data, except for CLOBs, accessed in the execution of a Teradata SQL statement has an attribute of CASESPECIFIC or NOT CASESPECIFIC, either by default or by explicit designation. Character string comparisons use this attribute to determine whether the comparison is case blind or case specific. Case specificity does not apply to CLOBs.

This is not an ANSI SQL:2011 compatible attribute—ANSI does all character comparisons as the equivalent of CASESPECIFIC.

The CASESPECIFIC attribute has higher precedence over the NOT CASESPECIFIC attribute:

IF … THEN the comparison is …
either argument is CASESPECIFIC case specific.
both arguments are NOT CASESPECIFIC case blind.

The exception is comparisons on GRAPHIC character data, which are always CASESPECIFIC.

To apply a case specification attribute to a character string, you can:
  • Use the default case specification for the session.
    IF the session mode is … THEN the default case specification is …
    ANSI CASESPECIFIC.
    Teradata NOT CASESPECIFIC.

    The exception is character data of type GRAPHIC, which is always CASESPECIFIC.

    Default case specification applies to all character data, including literals.

  • Use the CASESPECIFIC or NOT CASESPECIFIC phrase with a character column in a CREATE TABLE or ALTER TABLE statement.

    For example:

    CREATE TABLE Students
      (StudentID INTEGER
      ,Firstname CHAR(10) CASESPECIFIC
      ,Lastname CHAR(20) NOT CASESPECIFIC);

    Table columns carry the attribute assigned at the time the columns were defined or altered unless a CASESPECIFIC or NOT CASESPECIFIC phrase is used in their access.

  • Apply the CASESPECIFIC or NOT CASESPECIFIC phrase to a character expression in the comparison.

    For example, the following statement applies the CASESPECIFIC phrase to a character literal:

    SELECT * 
    FROM Students 
    WHERE Firstname = 'Ike' (CASESPECIFIC);

    Use this to override the default case specification for character data, or to override the case specification attribute assigned at the time a character column was defined or altered.

For case blind comparisons, any lowercase single byte Latin letters are converted to uppercase before comparison begins. The prepared strings are compared and any trailing pad characters are ignored.

A case blind comparison always considers lowercase and uppercase Cyrillic, Greek and full-width ASCII letters to be equivalent. To distinguish lowercase and uppercase Cyrillic, Greek, and fullwidth ASCII letters you must explicitly declare CASESPECIFIC comparison.

These options work for the KANJISJIS character set as if the data were first converted to the Unicode type and then the options applied.