DB2 Scalar functions - UPPER (locale sensitive)

UPPER (locale sensitive)

The UPPER function returns a string in which all characters have been converted to uppercase characters using the rules associated with the specified locale.

The schema is SYSIBM.

string-expression

An expression that returns a CHAR, VARCHAR, GRAPHIC, or VARGRAPHIC string.
If string-expression is CHAR or VARCHAR, the expression must not be FOR BIT DATA (SQLSTATE 42815).

locale-name

A character constant that specifies the locale that defines the rules for conversion to uppercase characters.
The value of locale-name is not case sensitive and must be a valid locale (SQLSTATE 42815).
For information about valid locales and their naming, see Locale names for SQL and XQuery.

code-units

An integer constant that specifies the number of code units in the result.
If specified, code-units must be an integer between:

1 and 32 672 if the string unit of the result is OCTETS
1 and 16 336 if the string unit of the result is double bytes or CODEUNITS16
1 and 8 168 if the string unit of the result is CODEUNITS32

otherwise an error (SQLSTATE 42815).
If code-units is not explicitly specified, it is implicitly the length attribute of string-expression.
If OCTETS is specified and the result is graphic data, the value of code-units must be even, otherwise an error is returned (SQLSTATE 428GC).
If OCTETS is specified and the string units of the result is CODEUNIT32, the value of code-units must be a multiple of 4 (SQLSTATE 428GC).
If CODEUNITS16 is specified and the string units of the result is CODEUNITS32, the value of code-units must be a multiple of 2 (SQLSTATE 428GC).

CODEUNITS16, CODEUNITS32, or OCTETS

Specifies the string unit of code-units.

CODEUNITS16 specifies that code-units is expressed in 16-bit UTF-16 code units.
CODEUNITS32 specifies that code-units is expressed in 32-bit UTF-32 code units.
OCTETS specifies that code-units is expressed in bytes.

If a string unit is not explicitly specified, the string unit of string-expression determines the unit that is used.
For more information about CODEUNITS16, CODEUNITS32, and OCTETS, see String units in built-in functions in Character strings.

The result of the function is VARCHAR if string-expression is CHAR or VARCHAR, and VARGRAPHIC if string-expression is GRAPHIC or VARGRAPHIC.
The string units of the result is the same as the string units of string-expression

The length attribute of the result is determined by the implicit or explicit value of code-units, the implicit or explicit string unit, the result data type, and the result string units, as shown in the following table:

Table 1. Length attribute of the result of UPPER as a function of string unit and result type

Data type and string units of result	Length attribute for `code-units` in CODEUNITS16	Length attribute for `code-units` in CODEUNITS32	Length attribute for `code-units` in OCTETS
VARCHAR in OCTETS	MIN(`code-units` * 3, 32672)	MIN(`code-units` * 4, 32672)	`code-units`
VARCHAR in CODEUNITS32	MIN(`code-units` / 2, 8168)	MIN(`code-units`, 8168)	MIN(`code-units` / 4, 8168)
VARGRAPHIC in CODEUNITS16 or double bytes	`code-units`	MIN(`code-units` * 2, 16336)	MIN(`code-units` / 2, 16336)
VARGRAPHIC in CODEUNITS32	MIN(`code-units` / 2, 8168)	MIN(`code-units`, 8168)	MIN(`code-units` / 4, 8168)

The actual length of the result might be greater than the length of string-expression.
If the actual length of the result is greater than the length attribute of the result, an error is returned (SQLSTATE 42815).
If the number of code units in the result exceeds the value of code-units, an error is returned (SQLSTATE 42815).

If string-expression is not in UTF-16, this function performs code page conversion of string-expression to UTF-16, and of the result from UTF-16 to the code page of string-expression.
If either code page conversion results in at least one substitution character, the result includes the substitution character, a warning is returned (SQLSTATE 01517), and the warning flag SQLWARN8 in the SQLCA is set to 'W'.

If the first argument can be null, the result can be null; if the first argument is null, the result is the null value.

Examples

Ensure that the characters in the value of column JOB in the EMPLOYEE table are returned in uppercase characters.
```
   SELECT UPPER(JOB
   ,     'en_US')
   FROM   EMPLOYEE
   WHERE  EMPNO = '000020'
```
The result is the value 'MANAGER'.
Find the uppercase characters for all the 'I' characters in a Turkish string.
```
   VALUES UPPER('IIii', 'tr_TR', CODEUNITS16)
```
The result is the string 'IIII'.
Find the uppercase form of the German 'ß' (sharp S).
```
   VALUES UPPER('ß', 'de', 2, CODEUNITS16)
```
The result is the string 'SS'. Note that code-units must be specified in this example, because there are more code units in the result than in string-expression.

Related reference

Character strings
UPPER scalar function