15.00 - UPPER - Teradata Database

Teradata Database SQL Functions, Operators, Expressions, and Predicates

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1145-015K
Language
English (United States)
Last Update
2018-09-24

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