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.
- 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).
- 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.
- An integer constant that specifies the number of code units in the result. If specified, this
must be an integer between:
The value that can be specified for code-units depends on which string units are used:
- 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
- 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 functionsin
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:
|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.
- Example 1: Ensure that the characters in the value of column
JOB in the EMPLOYEE table are returned in lowercase characters.
The result is the value 'manager'.
SELECT LOWER(JOB, 'en_US') FROM EMPLOYEE WHERE EMPNO = '000020'
- Example 2: Find the lowercase characters for all the 'I'
characters in a Turkish string.
The result is the string 'ıiıi'.
VALUES LOWER('Iİıi', 'tr_TR', CODEUNITS16)