15.00 - LOWER - Teradata Database

Teradata Database SQL Functions, Operators, Expressions, and Predicates

Teradata Database
Release Number
Content Type
Programming Reference
Publication ID
English (United States)



Returns a character string identical to character_string_expression, except that all uppercase letters are replaced with their lowercase equivalents.




Syntax element …

Specifies …


a character string or character string expression for which all uppercase characters are to be replaced with their lowercase equivalents.

ANSI Compliance

This is ANSI SQL:2011 compliant.

Argument Types

LOWER is valid only for character strings or 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 LOWER, 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 LOWER(arg):


Data Type


Same type as arg


The LOWER function returns the result in the same character set as the input argument. The exception is when the input is KANJI1 data; LOWER returns the result in the LATIN server character set.


In accordance with Teradata internationalization plans, KANJI1 support is deprecated and is to be discontinued in the near future. KANJI1 is not allowed as a default character set; the system changes the KANJI1 default character set to the UNICODE character set. Creation of new KANJI1 objects is highly restricted. Although many KANJI1 queries and applications may continue to operate, sites using KANJI1 should convert to another character set as soon as possible. “”

Usage Notes

See International Character Set Support for the internal mappings that Teradata Database uses for the LOWER function.

Teradata SQL has the type attribute NOT CASESPECIFIC that allows case blind comparisons, but the type attributes CASESPECIFIC and NOT CASESPECIFIC are Teradata extensions to the ANSI standard.

For ANSI portability, use the UPPER function for case blind comparisons with ANSI-compliant syntax. For details, see “UPPER” on page 1274.


The use of LOWER to return and store values is shown in the following example.

   SELECT LOWER (last_name) 
   FROM names;
   INSERT INTO names 
   SELECT LOWER(last_name),LOWER(first_name)
   FROM newnames;

The identical result is achieved with a USING phrase.

   USING (last_name CHAR(20),first_name CHAR(20))
   INSERT INTO names (LOWER(:last_name), LOWER(:first_name));