UPPER
Purpose
Returns a character string identical to character_string_expression, except that all lowercase letters are replaced by their uppercase equivalents.
Syntax
where:
|
Syntax element … |
Specifies … |
|
character_string_expression |
a character string or character string expression for which all lowercase characters are to be replaced by their uppercase equivalents. |
ANSI Compliance
This is ANSI SQL:2011 compliant.
Argument Types
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):
|
Data Type |
Heading |
|
Same type as arg |
Upper(arg) |
Usage Notes
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:
expression (UPPERCASE)
You can also replace characters with lowercase equivalents. For more information, see “LOWER” on page 1213.
Restrictions
UPPER does not convert multibyte characters to uppercase in the KANJI1 server character set.
Example
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:
SELECT emp_id
FROM employee
WHERE UPPER(emp_id) = UPPER(emp_ssn);
To compare with a string literal:
SELECT emp_id
FROM employee
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.
Example
The use of UPPER to store values is shown in the following examples:
INSERT INTO names
SELECT UPPER(last_name),UPPER(first_name)
FROM newnames;
or
USING (last_name CHAR(20),first_name CHAR(20))
INSERT INTO names
(UPPER(:last_name), UPPER(:first_name));
Example
This example shows that in the KANJI1 server character set, only single byte characters are converted to uppercase.
SELECT UPPER('abcd
');
The result is 'ABCD'.