Performing culturally correct case conversions

Rules for uppercase and lowercase conversion vary according to language and country. If you plan to use the UPPER or LOWER function, you need to ensure that Db2 uses the culturally correct casing rules. For example, you need to tell Db2 how to convert characters such as ß and ó to uppercase.

Before you begin

Before you use the UPPER or LOWER function on Unicode or ASCII data, you need to set up z/OS® Unicode Services.

Procedure

To ensure that Db2 uses the correct casing rules for a language and country:

When you use the UPPER function or LOWER function, ensure that Db2 uses the appropriate locale by performing one of the following actions:
  • Specify a value for the locale-name parameter of the UPPER or LOWER function:
    • For EBCDIC data, specify an LE locale, such as En_US or Fr_FR.
    • For Unicode and ASCII data, specify a locale value that is supported by the case conversion service of z/OS Unicode Services, such as EN_US. For a list of locale values that are supported by the case conversion service, see Locales supported for case service . Start of changeYou can also specify the values UNI, UNI_60, or UNI_90, which mean that the case conversion service of z/OS Unicode Services is to use the normal and special casing rules.End of change
  • If you do not specify a value for the locale-name parameter of the UPPER and LOWER function, ensure that the value of the CURRENT LOCALE LC_CTYPE special register is correct. You can change the value by using the SET CURRENT LOCALE LC_CTYPE statement.

As an alternative to using the UPPER function, you can use the TRANSLATE function with only one parameter. In both cases, the strings are converted to uppercase.

Example

Begin general-use programming interface information.
Example 1
The following statements show how to ensure that the German character ß is handled correctly when Db2 converts it to upper case. In uppercase, ß should be 'SS.'

The first set of statements creates a table, inserts one row, and confirms that the value Hegelstraße was properly inserted.

CREATE TABLE T1 (C1 VARCHAR(15)) VOLATILE CCSID UNICODE;
INSERT INTO T1 VALUES('Hegelstraße',1); 
SELECT C1 FROM T1 ;

The SELECT statement returns the following result:

C1
Hegelstraße

If you do not specify a locale when you use the UPPER function on this value, the result is technically incorrect, as shown in the following example. In upper case, the German ß should be converted to SS. 

SELECT UPPER(C1)AS C1 FROM T1 ;

This SELECT statement returns the following result:

C1
HEGELSTRAßE

The following query returns output with the German ß correctly converted to SS. 

SELECT UPPER(C1,'De_DE') AS C1 FROM T1 ;

This SELECT statement returns the following result:

C1
HEGELSTRASSE

This query works correctly, because the locale De_DE is passed as a parameter to the UPPER function.

Example 2
Suppose that table T1 contains the Unicode data Chrysóstomo in column C1. Assume that you issue the following query with the UPPER function.
SELECT UPPER(C1)AS C1 FROM T1 ;
If you did not add the CASE SPECIAL and CASE LOCALE statements to your conversion image when setting up z/OS Unicode Services, this query returns the following result:
CHRYSóSTOMO
However, after setting up the conversion image with the CASE SPECIAL and CASE LOCALE statements and setting the LOCALE special register, you get the following correct result:
CHRYSÓSTOMO
Be aware that the UPPER function can result in expansion if the text contains certain characters, such as ó in this example. Ensure that the result string is large enough to contain the result of the expression.
End general-use programming interface information.