LOWER (locale sensitive) scalar function

The schema is SYSIBM.

The LOWER function returns a string in which all characters have been converted to lowercase characters using the rules associated with the specified locale.

string-expression

An expression that returns a CHAR, VARCHAR, GRAPHIC, or VARGRAPHIC string. If string-expression is CHAR or VARCHAR, the expression must not be FOR BIT DATA (SQLSTATE 42815).

locale-name

A character constant that specifies the locale that defines the rules for conversion to lowercase characters. The value of locale-name is not case sensitive and must be a valid locale (SQLSTATE 42815). For information on valid locales and their naming, see "Locale names for SQL and XQuery".

code-units

An integer constant that specifies the number of code units in the result. If specified, code-units must be an integer between 1 and 32 672 if the result is character data, or between 1 and 16 336 if the result is graphic data (SQLSTATE 42815). If code-units is not explicitly specified, it is implicitly the length attribute of string-expression. If OCTETS is specified and the result is graphic data, the value of code-units must be even (SQLSTATE 428GC).

CODEUNITS16, CODEUNITS32, or OCTETS

Specifies the string unit of code-units.

CODEUNITS16 specifies that code-units is expressed in 16-bit UTF-16 code units. CODEUNITS32 specifies that code-units is expressed in 32-bit UTF-32 code units. OCTETS specifies that code-units is expressed in bytes.

If a string unit is not explicitly specified, the data type of the result determines the unit that is used. If the result is graphic data, code-units is expressed in two-byte units; otherwise, it is expressed in bytes. For more information about CODEUNITS16, CODEUNITS32, and OCTETS, see "String units in built-in functions" in "Character strings".

The result of the function is VARCHAR if string-expression is CHAR or VARCHAR, and VARGRAPHIC if string-expression is GRAPHIC or VARGRAPHIC.

The length attribute of the result is determined by the implicit or explicit value of code-units, the implicit or explicit string unit, and the result data type, as shown in the following table:

The actual length of the result might be greater than the length of string-expression. If the actual length of the result is greater than the length attribute of the result, an error is returned (SQLSTATE 42815). If the number of code units in the result exceeds the value of code-units, an error is returned (SQLSTATE 42815).

If string-expression is not in UTF-16, this function performs code page conversion of string-expression to UTF-16, and of the result from UTF-16 to the code page of string-expression. If either code page conversion results in at least one substitution character, the result includes the substitution character, a warning is returned (SQLSTATE 01517), and the warning flag SQLWARN8 in the SQLCA is set to 'W'.

If the first argument can be null, the result can be null; if the first argument is null, the result is the null value.

Examples:

• Ensure that the characters in the value of column JOB in the EMPLOYEE table are returned in lowercase characters.

     SELECT LOWER(JOB, 'en_US')
       FROM EMPLOYEE
       WHERE EMPNO = '000020'

  The result is the value 'manager'.
• Find the lowercase characters for all the 'I' characters in a Turkish string.

     VALUES LOWER('Iİıi', 'tr_TR', CODEUNITS16)

  The result is the string 'ıiıi'.