Case Criteria and Issues
The following table summarizes case criteria and issues.
| Case Specification | Description |
|---|---|
| CASESPECIFIC (CS) | In sorts or comparisons, lowercase characters are not converted to uppercase and are not equal to uppercase characters.
|
| 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.
|
Rules: All 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 make sure 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 CASESPECIFIC.
- A column typed as CHAR or VARCHAR CHARACTER SET GRAPHIC defaults to CASESPECIFIC when 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.
The performance of views, macros, and CHECK table constraints can involve parsing character string literals at execution time and the more common processing of character string literals in queries.
For more information, see SELECT.
Rules: 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 the query case specific, qualify the predicate with (CASESPECIFIC):
SELECT DataBaseName FROM DBC.Databases WHERE DataBaseName = 'dbc' (CASESPECIFIC);
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
- 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 complies with the ANSI SQL:2011 standard and is therefore preferable. NOT CASESPECIFIC is a Teradata extension to the standard.
- 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.