CASESPECIFIC Phrase

Teradata Vantage™ Data Types and Literals

brand
Software
Teradata Vantage
prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
category
Programming Reference
featnum
B035-1143-162K

Purpose

Specifies case for character data comparisons and collations.

Syntax



ANSI Compliance

CASESPECIFIC is a Teradata extension to the ANSI SQL:2011 standard.

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 “The SELECT Statement” 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.

Usage Rules

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.

Example: CASESPECIFIC Phrase

The following query returns a result only if a case specific comparison of the literal ‘Leidner P’ finds a match.

SELECT Name
FROM Employee
WHERE Name(CS) = 'Leidner P' ;

The literal ‘Leidner P’ might default to CS or NOT CS, depending on the current mode, but because the type modifier of the comparison is CS, the comparison is case specific irrespective of the session mode.

To ensure a comparison that is not case specific irrespective of the session mode, specify the query as follows.

SELECT Name
FROM Employee
WHERE Name (NOT CS) = 'Leidner P' (NOT CS) ;

Alternatively, you can specify the query using ANSI-compatible syntax as follows.

SELECT Name
FROM Employee
WHERE UPPER (Name) = 'LEIDNER P' ;

Example: CASESPECIFIC Comparison and Collation on Mixed Case Data

CASESPECIFIC comparison and collation on mixed case data can produce unintended results.

SELECT Last_Name
FROM SalesReps
ORDER BY Last_Name(CS) ;

might return one of the following sorted lists depending on the collation in effect for the session.

EBCDIC ASCII MULTINATIONAL
bart ACME ACME
fernandez ALBERT Albert
hill Albert ALBERT
Albert FARRAH bart
ACME Kimble FARRAH
ALBERT bart fernandez
FARRAH fernandez hill
Kimble hill Kimble