DB2 10.5 for Linux, UNIX, and Windows

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.


>>-LOWER--(--string-expression--,--locale-name--+---------------+--+--------------------+--)-><
                                                '-,--code-units-'  '-,--+-CODEUNITS16-+-'      
                                                                        +-CODEUNITS32-+        
                                                                        '-OCTETS------'

The schema is SYSIBM.

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 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, code-units must be an integer between:

1 and 32 672 if the string unit of the result is OCTETS
1 and 16 336 if the string unit of the result is double bytes or CODEUNITS16
1 and 8 168 if the string unit of the result is CODEUNITS32

otherwise an error is returned (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). If OCTETS is specified and the string units of the result is CODEUNIT32, the value of code-units must be a multiple of 4 (SQLSTATE 428GC). If CODEUNITS16 is specified and the string units of the result is CODEUNITS32, the value of code-units 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".

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