Case-Conversion Functions

The case-conversion functions perform lettercase conversion on alphabetic characters. In the default locale, only the ASCII characters in the ranges A - Z and a - z can be modified by these functions, which enable you to perform case-insensitive searches in your queries and to specify the format of the output.

The case-conversion functions are UPPER, LOWER, and INITCAP. The following diagram shows the syntax of these case-conversion functions.
Case-Conversion Functions

1 UPPER
1 LOWER
1 INITCAP
2  ( expression )
Element Description Restrictions Syntax
expression Expression returning a character string Must be a built-in character type. If a host variable, its length must be long enough to store the converted string. Expression

The expression must return a character data type. When a column expression is specified, the column data type returned by the database server is that of expression. For example, if the input type is CHAR, the output type is also CHAR.

Argument to these functions must be of the built-in data types.

In all locales, the byte length returned from the description of a column with a case-conversion function is the input byte length of the source string. If you use a case-conversion function with a multibyte expression argument, the conversion might increase or decrease the length of the string. If the byte length of the result string exceeds the byte length expression, the database server truncates the result string to fit into the byte length of expression.

Only characters designated as ALPHA class in the locale file are converted, and this occurs only if the locale recognizes the construct of lettercase.

If expression evaluates to NULL, the result of a case-conversion function is also NULL.

The database server treats a case-conversion function as an SPL routine in the following instances:
  • If it has no argument
  • If it has one argument, and that argument is a named argument
  • If it has more than one argument
  • If it appears in the Projection list with a host variable as an argument

If none of the conditions in the preceding list are met, the database server treats a case-conversion function as a system function.

The following example uses all the case-conversion functions in the same query to specify multiple output formats for the same value: 
Input value:

SAN Jose

Query:

SELECT City, LOWER(City), LOWER("City"), 
   UPPER (City), INITCAP(City) 
      FROM Weather;

Query output: 

SAN Jose   san jose   city   SAN JOSE   San Jose