LOWER (locale sensitive) scalar function

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

The schema is SYSIBM.

string-expression

An expression that returns a built-in character string, , or graphic string. If the expression returns a character string, the expression cannot specify FOR BIT DATA (SQLSTATE 42815).

locale-name

A character constant that specifies the locale that defines the rules for conversion to lowercase characters. This value is not case sensitive and must be a valid locale (SQLSTATE 42815). For information about 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, this must be an integer between:

1 and 32672, if the string unit of the result is OCTETS
1 and 16336, if the string unit of the result is double bytes or CODEUNITS16
1 and 8168, if the string unit of the result is CODEUNITS32

Otherwise, an error is returned (SQLSTATE 42815). The default is the length attribute of string-expression.

The value that can be specified for code-units depends on which string units are used:

If the string unit OCTETS is specified for the string expression and the result is graphic data, the value must be even (SQLSTATE 428GC).
If the string unit OCTETS is specified for the string expression and the string unit of the result is CODEUNIT32, the value must be a multiple of 4 (SQLSTATE 428GC).
If the string unit CODEUNITS16 is specified for the string expression and the string unit of the result is CODEUNITS32, the value must be a multiple of 2 (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 string unit of string-expression determines the unit that is used. For more information about CODEUNITS16, CODEUNITS32, and OCTETS, see String units in built-in functions in Character strings.

Result

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

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

Table 1. Length attribute of the result of LOWER as a function of string unit and result type
Data type and string units of result	Length attribute for `code-units` in CODEUNITS16	Length attribute for `code-units` in CODEUNITS32	Length attribute for `code-units` in OCTETS
VARCHAR in OCTETS	MIN(`code-units` * 3, 32672)	MIN(`code-units` * 4, 32672)	`code-units`
VARCHAR in CODEUNITS32	MIN(`code-units` / 2, 8168)	MIN(`code-units`, 8168)	MIN(`code-units` / 4, 8168)
VARGRAPHIC in CODEUNITS16 or double bytes	`code-units`	MIN(`code-units` * 2, 16336)	MIN(`code-units` / 2, 16336)
VARGRAPHIC in CODEUNITS32	MIN(`code-units` / 2, 8168)	MIN(`code-units`, 8168)	MIN(`code-units` / 4, 8168)

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

Example 1: 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'.
Example 2: 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'.