Case Sensitivity - Analytics Database - Teradata Vantage

SQL Functions, Expressions, and Predicates

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-01-12
dita:mapPath
obm1628111499646.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
kby1472250656485
lifecycle
latest
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.