Returns a character string identical to character_string_expression, except that all lowercase letters are replaced by their uppercase equivalents.
Syntax element …
a character string or character string expression for which all lowercase characters are to be replaced by their uppercase equivalents.
This is ANSI SQL:2011 compliant.
UPPER is valid only for character strings and character string expressions. The function does not accept CLOBs and non-character arguments.
By default, Teradata Database performs implicit type conversion on UDT arguments that have implicit casts to predefined character types.
To define an implicit cast for a UDT, use the CREATE CAST statement and specify the AS ASSIGNMENT clause. For more information on CREATE CAST, see SQL Data Definition Language.
Implicit type conversion of UDTs for system operators and functions, including UPPER, is a Teradata extension to the ANSI SQL standard. To disable this extension, set the DisableUDTImplCastForSysFuncOp field of the DBS Control Record to TRUE. For details, see Utilities: Volume 1 (A-K).
For more information on implicit type conversion of UDTs, see Chapter 13: “Data Type Conversions.”
Result Type and Attributes
Here are the default result type and attributes for UPPER(arg):
Same type as arg
The UPPER function allows users who want ANSI portability to have case blind comparisons with ANSI-compliant syntax.
This function is treated the same as the following obsolete form:
You can also replace characters with lowercase equivalents. For more information, see “LOWER” on page 1213.
UPPER does not convert multibyte characters to uppercase in the KANJI1 server character set.
Consider the following table definition where the character columns have CASESPECIFIC attributes:
CREATE TABLE employee
(last_name CHAR(32) CASESPECIFIC
,city CHAR(32) CASESPECIFIC
,emp_id CHAR(9) CASESPECIFIC
,emp_ssn CHAR(9) CASESPECIFIC);
To compare on a case blind basis:
WHERE UPPER(emp_id) = UPPER(emp_ssn);
To compare with a string literal:
WHERE UPPER(city) = 'MINNEAPOLIS';
Teradata SQL also has the data type attribute NOT CASESPECIFIC, which allows case blind comparisons. Note that the data type attributes CASESPECIFIC and NOT CASESPECIFIC are Teradata extensions to the ANSI standard.
The use of UPPER to store values is shown in the following examples:
INSERT INTO names
USING (last_name CHAR(20),first_name CHAR(20))
INSERT INTO names
This example shows that in the KANJI1 server character set, only single byte characters are converted to uppercase.
The result is 'ABCD'.