Usage Notes - Advanced SQL Engine - Teradata Database

SQL Data Types and Literals

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-27
dita:mapPath
tpf1598412463935.ditamap
dita:ditavalPath
tpf1598412463935.ditaval
dita:id
B035-1143
lifecycle
previous
Product Category
Teradata Vantage™

Case Criteria and Issues

The following table summarizes case criteria and issues.

IF the case specification is … THEN …
CASESPECIFIC (CS) In sorts or comparisons, lowercase characters are not converted to uppercase and are not equal to uppercase characters.
  • If a column is defined as CASESPECIFIC, characters entered as ‘aaa’ are not equivalent to ‘AAA’ when used in a unique index.
  • Applications that conform to ANSI should define columns for ANSI mode and use the UPPER function to force comparisons that are not case specific.
NOT CASESPECIFIC (NOT CS) In sorts or comparisons, lowercase characters are converted to uppercase. ‘aaa’ is equivalent to ‘AAA’.

’AAA’, ’aaa’, ’AaA’, and ’aAA’ are all equivalent as unique indexes.

Neither CASE SPECIFIC nor NOT CASESPECIFIC The session mode determines the attribute assigned by default.
  • In ANSI mode, CASESPECIFIC is set.
  • In Teradata mode (except for CHAR or VARCHAR CHARACTER SET GRAPHIC data), NOT CASESPECIFIC is set.

Rules: All Modes

The following set of rules applies to CASESPECIFIC and NOT CASESPECIFIC in both ANSI and Teradata modes.
  • CASESPECIFIC specifies that comparisons are case-specific.
  • NOT CASESPECIFIC specifies that comparisons are not case-specific.
  • If neither CASESPECIFIC nor NOT CASESPECIFIC is specified as part of the column definition, then you can specify the option in an SQL request to ensure that the statement behaves as intended. By including an explicit CASESPECIFIC (or CS) qualifier in your SQL statement, you override the case specificity for a column.
  • In character string comparisons, if either of the strings being compared is CASESPECIFIC, then the comparison is always CASESPECIFIC.
  • A column typed as CHAR or VARCHAR CHARACTER SET GRAPHIC defaults to CASESPECIFIC when it is created.
  • To create a non-CASESPECIFIC CHAR or VARCHAR CHARACTER SET GRAPHIC column, you must specify a NOT CASESPECIFIC clause in the column definition. You can also use NOT CASESPECIFIC in a comparison predicate.
  • KANJI1 data only handles NOT CASESPECIFIC for characters in the range A–Z. All other server character sets handle all data correctly.
  • The following data types do not support CASESPECIFIC or NOT CASESPECIFIC:
    • CLOBs
    • UDTs
  • Character data is stored as typed unless the UPPERCASE phrase is specified. See UPPERCASE Phrase.

Note that the performance of views, macros, and CHECK table constraints can involve parsing character string literals at execution time as well as the more common processing of character string literals in queries.

For more information, see SELECT in Teradata Vantage™ - SQL Data Manipulation Language, B035-1146.

Rules: Teradata Mode

The following rules apply to CASESPECIFIC and NOT CASESPECIFIC in Teradata mode.
  • All character types except CHAR or VARCHAR CHARACTER SET GRAPHIC default to NOT CASESPECIFIC.

    You can override this default in a query by specifying (CASESPECIFIC) immediately following the CHARACTER data to be compared.

    For example, the following query is not case specific in Teradata mode:

    SELECT DataBaseName
    FROM DBC.Databases
    WHERE DataBaseName = 'dbc';

    To make it case specific, qualify the predicate with (CASESPECIFIC):

    SELECT DataBaseName
    FROM DBC.Databases
    WHERE DataBaseName = 'dbc' (CASESPECIFIC);

    Note that the first example returns one row and the second example returns no rows.

  • CASESPECIFIC supports all letters in the ISO 10646 repertoire. See also UPPERCASE Phrase.

Rules: ANSI Mode

The following rules apply to CASESPECIFIC and NOT CASESPECIFIC in ANSI mode.
  • All character data defaults to CASESPECIFIC.
  • To make comparisons that are not case specific using ANSI SQL:2011 syntax, you must apply the UPPER function to any character string value that may contain lower case Latin letters.
  • To make comparisons that are not case specific using Teradata syntax, you can apply the NOT CASESPECIFIC specification to the appropriate character string. The UPPER function is preferable because it complies with the ANSI SQL:2011 standard. NOT CASESPECIFIC is a Teradata extension to the standard.

    For more information on UPPER, see Teradata Vantage™ - SQL Functions, Expressions, and Predicates, B035-1145.

  • CASESPECIFIC supports all letters in the ISO 10646 repertoire.

    If you declare a character column to be NOT CASESPECIFIC and type ‘ά’ (GREEK SMALL LETTER ALPHA WITH TONOS), then that letter is stored as ‘ά’ but during comparison compares equal to ‘Ά’ (GREEK CAPITAL LETTER ALPHA WITH TONOS).

    The same applies to the (NOT CASESPECIFIC) qualifier in a SQL predicate.

    The following SQL predicate evaluates to TRUE.
    'ά' (NOT CASESPECIFIC) = 'Ά'

    Without the (NOT CASESPECIFIC) qualifier, the same predicate evaluates to FALSE.