UPPER, LOWER and case functions in DB2

UPPER and UCASE

UPPER returns a string in which characters have been converted to uppercase. Schema is SYSIBM.

1
UPPER(string-expression [, locale-name-string [, integer]])

• string-expression — a built-in character or graphic string. Must not be a CLOB or DBCLOB in the forms that IBM documents as disallowed for those LOB types on this function. If the argument is null, the result is null.
• locale-name-string — optional. A valid locale name, a blank string, or one of the Unicode mode names below. Host variables are allowed; CLOB/DBCLOB are not. If omitted, Db2 uses CURRENT LOCALE LC_CTYPE.
• integer — optional third argument used with certain locale forms as documented for length / conversion control in the SQL Reference. Most beginner calls use one or two arguments.

UCASE is a synonym for UPPER. IBM’s wording: use UPPER for conformance to the SQL standard. Both resolve to the same built-in behaviour.

1
2
3
4
5
SELECT UPPER(LASTNAME) AS LAST_UP,
       UCASE(FIRSTNME) AS FIRST_UP
  FROM DSN8C10.EMP
 WHERE EMPNO = '000010';
-- HAAS, CHRISTINE

LOWER and LCASE

LOWER is the inverse: convert to lowercase with the same locale rules. LCASE is a synonym for LOWER. Prefer LOWER in new SQL.

1
2
3
4
LOWER(string-expression [, locale-name-string [, integer]])
 
SELECT LOWER(LASTNAME) FROM DSN8C10.EMP WHERE EMPNO = '000010';
-- haas  (blank-locale Latin folding)

Round-trip is not always identity. Unicode special casing can map one character to two (German ß and SS is the usual textbook case under full Unicode rules). Blank-locale EBCDIC folding of A–Z / a–z is a clean round-trip.

Locale names and Unicode modes

IBM’s performance advice: specify a blank string unless the data must follow a specific locale. Cultural locales (En_US, Fr_CA, …) invoke ICU-style rules and cost more. UNI* names must not be used when string-expression is EBCDIC data — typical z/OS CHAR/VARCHAR in CCSID 37 or 500 is EBCDIC.

1
2
3
4
5
6
7
8
-- Fast path for EBCDIC Latin names (blank locale)
SELECT UPPER(LASTNAME, ' ') FROM DSN8C10.EMP;
 
-- Cultural English (United States), when the locale is installed
SELECT UPPER(LASTNAME, 'En_US') FROM DSN8C10.EMP;
 
-- Unicode data only
SELECT UPPER(UNAME, 'UNI_SIMPLE') FROM UNICODE_NAMES;

CURRENT LOCALE LC_CTYPE

The DECP field LOCALE LC_CTYPE sets the system default (0–50 characters, default blank). Applications can set the special register CURRENT LOCALE LC_CTYPE. UPPER, LOWER, and TRANSLATE all honour it when you omit the locale argument. Two programs can therefore fold the same column differently if one session set Fr_CA and another left the register blank. For batch COBOL that must be reproducible, pass an explicit locale (often a blank string) in the function call.

1
2
SET CURRENT LOCALE LC_CTYPE = 'En_US';
SELECT UPPER(CITY) FROM ADDRESSES;

Case-insensitive search

The classic pattern:

1
2
3
SELECT EMPNO, LASTNAME
  FROM DSN8C10.EMP
 WHERE UPPER(LASTNAME) = 'HAAS';

That finds Haas, HAAS, and haas under blank-locale Latin rules. Costs:

• The predicate is on an expression, so a plain index on LASTNAME may not be used for matching. EXPLAIN it.
• Better designs: store LASTNAME already folded, or add a generated column LASTNAME_U GENERATED ALWAYS AS (UPPER(LASTNAME, ' ')) and index that, or create an expression index where your Db2 version and shop standards allow it.
• Fold the host variable too. Comparing UPPER(COL) to a mixed-case host variable still misses rows. In COBOL, MOVE FUNCTION UPPER-CASE(WS-NAME) to the host field, or use UPPER(:HV) on both sides consistently — UPPER on a host variable is computed once per row unless the optimizer caches it, so folding in COBOL is cheaper.

1
2
-- Both sides folded (literal already upper)
WHERE UPPER(LASTNAME, ' ') = UPPER(:SEARCH-NAME, ' ')

CHAR padding and case

UPPER does not trim. UPPER of CHAR(10) ABC is ABC followed by seven uppercase blanks, which are still blanks. Combine with RTRIM when you concatenate or when VARCHAR comparison visibility matters:

1
2
SELECT UPPER(RTRIM(LASTNAME)) CONCAT ', ' CONCAT UPPER(RTRIM(FIRSTNME))
  FROM DSN8C10.EMP;

EBCDIC versus Unicode on z/OS

Most legacy tables are EBCDIC. Blank-locale UPPER is exactly what those tables need for A–Z. Accented EBCDIC code points (depending on CCSID) are not folded by the blank locale. If your French names live in EBCDIC and you need É from é, that is a locale / TRANSLATE / Unicode conversion project — not a one-argument UPPER.

Unicode columns (CCSID 1208 UTF-8, 1200 UTF-16 graphic, and so on) can use UNI_SIMPLE or a language locale. Do not copy a UNI_SIMPLE example from a LUW blog onto an EBCDIC LASTNAME column; Db2 will reject that combination.

TRANSLATE as a cousin

TRANSLATE can also change case if you supply from- and to-alphabets, and it honours CURRENT LOCALE LC_CTYPE in some forms. It is the right tool for “change these digits” or “map / to -.” For case folding, UPPER and LOWER state the intent and pick up Unicode modes. Use TRANSLATE when you have a custom mapping table, not as a substitute for UPPER in new code.

COBOL

COBOL has intrinsic FUNCTION UPPER-CASE and LOWER-CASE for host strings. You can fold in the program or in SQL. Folding in SQL keeps one rule for SPUFI, QMF, Java, and COBOL. Folding in COBOL keeps the predicate sargable on the raw column if you store mixed case but search with a pre-folded host variable against a folded column. Typical static SQL:

1
2
3
4
5
6
           EXEC SQL
                SELECT EMPNO, LASTNAME
                  INTO :EMPNO, :LASTNAME
                  FROM DSN8C10.EMP
                 WHERE UPPER(LASTNAME, ' ') = :LAST-UP
           END-EXEC.

LAST-UP should already be uppercase PIC X data. Match VARCHAR host variables to VARCHAR results; UPPER of CHAR is still a character string with the source’s length attribute in the usual blank-locale case.

Nulls and empty strings

UPPER(NULL) is null. UPPER('') is the empty string. A CHAR that is all blanks stays all blanks (still blanks after “uppercasing”). Predicates should still use IS NULL when you mean missing, not UPPER(COL) = ''.

Explain It Like I'm Five

UPPER is a teacher with a red pen who rewrites every little letter as a big letter. LOWER does the opposite. If you do not tell the teacher which country's handwriting rules to use, the teacher only knows plain A–Z and leaves fancy accent marks alone — that is the blank locale, and it is fast. If you say “use French rules” or “use Unicode rules,” the teacher knows more letters but works slower, and those Unicode rulebooks are not for the old EBCDIC alphabet cards most z/OS tables still use. UCASE is just another nickname for the same uppercase teacher.

Exercises

1. Write a query that returns LASTNAME in uppercase and FIRSTNME in lowercase for department A00.
2. Explain the difference between UPPER(LASTNAME) and UPPER(LASTNAME, 'UNI_SIMPLE') on an EBCDIC column.
3. Rewrite a case-insensitive search so both the column and the host variable are folded with an explicit blank locale.
4. Why might WHERE UPPER(LASTNAME) = 'HAAS' scan more than WHERE LASTNAME = 'HAAS'? What design fixes it?
5. Combine RTRIM and UPPER to build LAST, FIRST display lines without CHAR padding gaps.