LOWER
The LOWER function returns a string in which all the characters are converted to lowercase characters.
>>-LOWER(string-expression-+-----------------------+-+------------+-)->< '-,--locale-name-string-' '-,--integer-'
The schema is SYSIBM.
- string-expression
- An expression that specifies the string to be
converted. string-expression must return a value that is a built-in
character or graphic string. A character string argument must not be a CLOB, and a graphic string
argument must not be a DBCLOB. If string-expression is an EBCDIC graphic
string, a blank string must not be specified for locale-name-string. If
string-expression is bit data,
locale-name-string must not be specified.
The argument can also be a numeric data type. The numeric argument is implicitly cast to a VARCHAR data type.
- locale-name-string
- A string constant or a string host variable other than a CLOB or DBCLOB that specifies a valid
locale name. If locale-name-string is not in EBCDIC, it is converted to
EBCDIC. The length of locale-name-string must be between 1 and 255 bytes
of the EBCDIC representation. The value of locale-name-string is not case
sensitive and must be a valid locale. For information on locales and their naming conventions, see
z/OS C/C++
Programming Guide. Some examples of
locales include:
- Fr_BE
- Fr_FR@EURO
- En_US
- Ja_JP
The conversion process is determined by the value that is specified for the locale name, as follows:
- blank — SBCS uppercase characters A-Z are converted to SBCS lowercase characters a-z, and characters with diacritical marks are not converted. If the string contains MIXED or DBCS characters, full-width Latin uppercase characters A-Z are converted to full-width lowercase characters a-z. For optimal performance, specify a blank string unless your data must be processed by using the rules that are defined by a specific locale.
- UNI — The conversion uses both the NORMAL and SPECIAL casing capabilities as described in Select the conversions. You must not specify UNI when string-expression is EBCDIC data.
- locale name — The locale defines the rules for conversion to lowercase characters.
The value of the host variable must not be null. If the host variable has an associated indicator variable, the value of the indicator variable must not indicate a null value. The locale name must be:
- left justified within the host variable
- padded on the right with blanks if its length is less than that of the host variable and the host variable is in fixed length character or graphic data type
If locale-name-string is not specified, the locale is determined by special register CURRENT LOCALE LC_CTYPE. For information about the special register, see CURRENT LOCALE LC_CTYPE. However, if an index references the LOWER function, the local is determined as follows (in order) to determine if the index can be used:
- At prepare time — using the value in the CURRENT LOCALE LC_CTYPE special register
- At bind time — using the value in the LOCALE LC_CTYPE field on installation panel DSNTIPF
If the index is chosen in the access path, the locale in the CURRENT LOCALE LC_CTYPE special register must remain the same at run time, and prepare or bind time. To avoid this dependency, do not omit locale-name-string.
If the LOWER function is referenced in an expression-based index, locale-name-string must be specified. See the examples section for an example of how the index can be used in a query.
- integer
- An integer value that specifies the length attribute of the result. If specified,
integer must be an integer constant between 1 and 32704 bytes in the
representation of the encoding scheme of string-expression.
If integer is not specified, the length attribute of the result is the same as the length of string-expression.
For Unicode data, usage of the LOWER function can result in expansion if certain characters are processed. For example, LOWER ('Ì') —UX'00CC'— will result in UX'006903070300' (if the LT_LT locale is in effect at the time). You should ensure that the result length is large enough to contain the result of the expression.
The result can be null; if the argument is null, the result is the null value.
LCASE is a synonym for LOWER.
SELECT LCASE(:NAME)
FROM SYSIBM.SYSDUMMY1;
The result is the
value 'christine smith'.SELECT LOWER('Ì')
FROM SYSIBM.SYSDUMMYU;
This would result in an error
because of the expansion that occurs when certain Unicode characters are processed. To avoid the
error, you would need to use the following statement instead:
SELECT LOWER(VARCHAR('Ì', 3))
FROM SYSIBM.SYSDUMMYU;
The result of the
preceding statement is the value UX'006903070300'. CREATE INDEX EMPLOYEE_NAME_LOWER
ON EMPLOYEE (LOWER(LASTNAME, 'LT_LT', 60),
LOWER(FIRSTNAME, 'LT_LT', 60),
ID);
CREATE INDEX LNAME
ON TI (LOWER(LASTNAME, ' '));
SELECT LOWER(LASTNAME, ' ')
FROM TI
WHERE LOWER(LASTNAME, ' ') = 'smith';
CREATE INDEX LNAME
ON TI (LOWER(LASTNAME, 'FR_CA'));
If the
query is a dynamic statement and the CURRENT LOCALE LC_CTYPE special register contains
'FR_CA': SELECT LASTNAME
FROM TI
WHERE LOWER(LASTNAME)='smith';
At prepare time, locale 'FR_CA' in CURRENT LOCALE LC_CTYPE is used for LOWER(LASTNAME) in the predicate to determine whether index LNAME can be used for index access. If index LNAME is used in access path selection, at run time, the locale in CURRENT LOCALE LC_CTYPE must remain the same.
SELECT LASTNAME
FROM TI
WHERE LOWER(LASTNAME)='smith';
At bind time, local 'FR_CA' in the LOCALE LC_CTYPE file of installation panel DSNTIPF is used for LOWER(LASTNAME) in the predicate to determine whether index LNAME is used for index access. If index LNAME is chosen in access path selection, the locale in the CURRENT LOCALE LC_CTYPE special register must contain 'FR_CA'.