UPPERCASE Phrase

Teradata Vantage™ Data Types and Literals

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

Purpose

Specifies that character data for a column is stored as uppercase.

Also used in CAST and with Teradata conversion syntax to convert a value to uppercase.

Syntax



ANSI Compliance

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

Rules: All Modes

The following rules apply to UPPERCASE in both ANSI and Teradata modes.
  • You can specify the UPPERCASE option in an SQL request to override the stored case specificity assignment for a column.
  • If a column is declared to be UPPERCASE, then lowercase letters are converted and stored as their uppercase equivalents.

    Note that the KANJI1 server character set only converts the 26 letters a-z to uppercase.

  • UPPERCASE supports all letters in the ISO 10646 repertoire.

    If you declare a character column to be UPPERCASE and type ‘ά’ (GREEK SMALL LETTER ALPHA WITH TONOS), then that character is translated and stored as ‘Ά’ (GREEK CAPITAL LETTER ALPHA WITH TONOS).

    The same applies to the (UC) qualifier.

    The following SQL predicate evaluates to TRUE.
    'ά' (UC) = 'Ά'
  • The following data types do not support UPPERCASE
    • CLOBs
    • UDTs

UPPERCASE Phrase and UPPER Function

The UPPER function is defined by the ANSI SQL:2011 standard and is not the same as declaring a value to be UPPERCASE. For further information, see “UPPER” in Teradata Vantage™ SQL Functions, Expressions, and Predicates, B035-1145.

Usage Rules

The following table summarizes case criteria and issues.

Case Specification Usage Rules
UPPERCASE not specified

NOT CASESPECIFIC specified

The following are all equivalent as unique indexes:
  • AAA
  • aaa
  • AaA
  • aAA
UPPERCASE (UC) Character data is stored in uppercase regardless of the case in which it was typed.

Because of the conversion, if a column is defined as UPPERCASE, characters typed as 'aaa' are stored as 'AAA' and therefore are equivalent to 'AAA' when used in a unique index.

Example: UPPERCASE Phrase

The Gender column is created using the UPPERCASE option.

Gender CHAR UPPERCASE

If Gender data is entered in lowercase as shown in the following INSERT statement, then it is stored and returned in uppercase.

INSERT INTO Employee (Name, EmpNo, Gender ...
 VALUES ('Smith', 10021, 'f', ...);
   
SELECT Gender
FROM Employee;
   Gender
   F

Example: UPPERCASE Phrase With the Concatenation Operator

If used with the concatenation (||) operator, UC must be enclosed in parentheses, and be placed immediately after the column name.

SELECT (City_name (UC)) || ', ' || State, Population
FROM World
WHERE Country = 'USA'
ORDER BY Population ;