15.10 - CASESPECIFIC Phrase - Teradata Database

Specifies case for character data comparisons and collations.

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

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” on page 222.

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 SQL Data Manipulation Language.

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” on page 222.

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 SQL Functions, Operators, Expressions, and Predicates.

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 TONOS), then that letter is stored as ‘’ but during comparison compares equal to ‘'Α’ (GREEK CAPITAL LETTER ALPHA 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.

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. For an example, see “Example 1” on page 220.
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.

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' ;

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