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
Start of changeAn 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.

Start of changeThe argument can also be a numeric data type. The numeric argument is implicitly cast to a VARCHAR data type.End of change

End of change
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

Start of changeThe conversion process is determined by the value that is specified for the locale name, as follows:End of change

Start of change
  • 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.
End of change

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.

Example 1: Return the characters in the value of host variable NAME in lowercase. NAME has a data type of VARCHAR(30) and a value of 'Christine Smith'. Assume that the locale in effect is blank.
  SELECT LCASE(:NAME)
    FROM SYSIBM.SYSDUMMY1;
The result is the value 'christine smith'.
Example 2: Return the lowercase of 'Ì'. Assume that the locale in effect is LT_LT.
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'.
Example 3: Create an index EMPLOYEE_NAME_LOWER for table EMPLOYEE based on built-in function LOWER with locale name 'LT_LT'.
  CREATE INDEX EMPLOYEE_NAME_LOWER
    ON EMPLOYEE (LOWER(LASTNAME, 'LT_LT', 60),
                 LOWER(FIRSTNAME, 'LT_LT', 60),
                 ID);
Example 4: Create an index LNAME for table T1 based on the LOWER function with the default local value, ' '. Then specify the same expression in a query.
   CREATE INDEX LNAME
     ON TI (LOWER(LASTNAME, ' '));

   SELECT LOWER(LASTNAME, ' ')
     FROM TI
     WHERE LOWER(LASTNAME, ' ') = 'smith'; 
Example 5: Create an index LNAME that is based on the LOWER function with a locale name 'FR_CA' for the table T1. Then specify the same expression in a query except locale-name-string is omitted.
   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.

If the query is a static statement and locale 'FR_CA' has been set on the LOCALE LC_CTYPE field of installation panel DSNTIPF:
   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'.