UPPER scalar function

The UPPER function returns a string in which all the characters have been converted to uppercase characters.

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.

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 in the range 1–255 bytes of the EBCDIC representation. The value of locale-name-string is not case sensitive and must be a valid locale. For information about locales and their naming conventions, see Locale naming conventions. 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 lowercase characters a-z are converted to SBCS uppercase characters A-Z, and characters with diacritical marks are not converted. If the string contains MIXED or DBCS characters, full-width lowercase characters a-z are converted to full-width Latin uppercase 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.
UNI_60: The conversion uses Unicode Standard 6.0.0 and the NORMAL casing capability, as described in Select the conversions. You must not specify UNI_60 when string-expression is EBCDIC data.
UNI_90: The conversion uses Unicode Standard 9.0.0 and the NORMAL casing capability, as described in Select the conversions. You must not specify UNI_90 when string-expression is EBCDIC data.
UNI_SIMPLE: Case conversions use the NORMAL casing capabilities as described in Select the conversions. UNI_SIMPLE cannot be used with EBCDIC data.
locale name: The locale defines the rules for conversion to uppercase 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 CHAR 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 special register.

If the UPPER function is referenced in an expression-based index, locale-name-string must be specified

integer

An integer value that specifies the length attribute of the result. If specified, integer must be an integer constant in the range 1–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 UPPER function can result in expansion if certain characters are processed. For example, UPPER(UX'FB03') will result in UX'004600460049'. You should ensure that the result string 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.

Notes

Syntax alternatives:: UCASE is a synonym for UPPER. UPPER should be used for conformance to the SQL standard.

Examples

Example 1:

Return the string 'abcdef' in uppercase characters. Assume that the locale in effect is blank.

   SELECT UPPER('abcdef')
     FROM SYSIBM.SYSDUMMY1

The result is the value 'ABCDEF'.

Example 2:

Return the string 'ffi' in the uppercase characters ('FFI'). Assume that the locale in effect is "UNI".

SELECT UPPER(UX'FB03')
  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 UPPER(CAST(UX'FB03' AS VARCHAR(3))
  FROM SYSIBM.SYSDUMMYU;

The result of the preceding statement is the value 'FFI'.

Example 3:

Create an index EMPLOYEE_NAME_UPPER for table EMPLOYEE based on built-in function UPPER with locale name 'Fr_FR@EURO'.

   CREATE INDEX EMPLOYEE_NAME_UPPER
      ON EMPLOYEE (UPPER(LASTNAME, 'Fr_FR@EURO', 60),
                   UPPER(FIRSTNAME, 'Fr_FR@EURO', 60),
                   ID);

The result is the value 'ABCDEF'.