Case-Conversion Functions

The case-conversion functions perform lettercase conversion on alphabetic characters. In the default locale, only the ASCII characters 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:
 
|--+-UPPER---+--(--expression--)--------------------------------|
   +-LOWER---+
   '-INITCAP-'
 

The expression must return a character data type. When the column is described, the 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 is 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 a 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

UPPER Function

The UPPER function accepts an expression argument and returns a character string in which every lowercase alphabetical character in the expression is replaced by a corresponding uppercase alphabetic character.

The following example uses the UPPER function to perform a case-insensitive search on the lname column for all employees with the last name of Curran:

SELECT title, INITCAP(fname), INITCAP(lname) FROM employees
   WHERE UPPER (lname) = "CURRAN"

Because the INITCAP function is specified in the projection list, the database server returns the results in a mixed-case format. For example, the output of one matching row might read: accountant James Curran.

LOWER Function

The LOWER function accepts an expression argument and returns a character string in which every uppercase alphabetic character in the expression is replaced by a corresponding lowercase alphabetic character.

The following example shows how to use the LOWER function to perform a case-insensitive search on the City column. This statement directs the database server to replace all instances (that is, any variation) of the words san jose, with the mixed-case format, San Jose.

UPDATE Weather SET City = "San Jose" 
WHERE LOWER (City) = "san jose";

INITCAP Function

The INITCAP function returns a copy of the expression in which every word in the expression begins with an uppercase letter. With this function, a word begins after any character other than a letter. Thus, in addition to a blank space, symbols such as commas, periods, colons, and so on, introduce a new word.

For an example of the INITCAP function, see UPPER Function.