String functions overview in DB2

String function categories

IBM’s SQL Reference lists dozens of scalars. Group them by job so you can find the right name:

Slice and extract

SUBSTR(string, start [, length]) takes a substring on a byte-count basis for character and binary strings (a graphic “character” is a DBCS character). Start is 1-based. Mixed EBCDIC data can be split in the middle of a shift-out/shift-in sequence because SUBSTR does not parse mixed encoding.

SUBSTRING(string, start, length, CODEUNITS16 | CODEUNITS32 | OCTETS) is the Unicode-aware cousin. Use CODEUNITS32 when “two characters” must mean two letters even if one is ü or a supplementary character.

1
2
3
4
SELECT SUBSTR(FIRSTNME, 1, 3) AS FIRST3,
       SUBSTRING(FIRSTNME, 1, 2, CODEUNITS32) AS FIRST2_CHARS
  FROM DSN8C10.EMP
 WHERE EMPNO = '000130';

LEFT / RIGHT (and STRLEFT / STRRIGHT) take a prefix or suffix. INSERT splices a string into another. REPEAT / SPACE build filler. LPAD / RPAD pad to a length.

Concatenate

CONCAT(x, y) and the || operator join two strings. Types must be compatible (character with character, graphic with graphic, binary with binary, with documented promotions). Numeric arguments are often cast to VARCHAR first. Concatenation of many pieces is nested CONCAT or a chain of ||.

1
2
3
SELECT CONCAT(CONCAT(FIRSTNME, ' '), LASTNAME) AS FULL_NAME,
       FIRSTNME || ' ' || LASTNAME AS FULL_NAME2
  FROM DSN8C10.EMP;

Trim and strip

STRIP and TRIM remove leading and/or trailing characters (often blank). LTRIM / RTRIM are the one-sided forms. Default strip character is blank, but TRIM can name another character. CHAR columns are padded with blanks to the length attribute—TRIM is how reports stop looking like “SMITH ”.

Search and length

LOCATE / POSITION find the start of a substring. LENGTH returns a large integer length whose unit depends on the type (bytes for character/binary, DBCS characters for graphic). OCTET_LENGTH is explicitly bytes. CHARACTER_LENGTH (CHAR_LENGTH) can specify string units like SUBSTRING.

Case and translate

UPPER / UCASE and LOWER fold case according to the CCSID’s case mapping—not always a naive ASCII +32. TRANSLATE maps characters; REPLACE substitutes substrings.

Convert and represent

CHAR and VARCHAR convert dates, numbers, and strings to character form (format options on CHAR for dates). HEX shows bytes. DIGITS is the character form of a number’s digits. CHR / ASCII_CHR build a character from a code point. ASCII_STR / EBCDIC_STR / UNICODE_STR move among encodings with substitution for unmappable characters.

Common return types

Matching the input family is the default for slice-style functions (IBM’s SUBSTRING table is the pattern):

• Length attribute — for SUBSTR with an explicit length, the result length attribute is that length (VARCHAR). Without length, rules use the source length attribute minus start + 1
• Fixed vs varying — CHAR() returns CHAR; most others return VARCHAR even when the input was CHAR, because the actual length varies
• INTEGER results — LENGTH, OCTET_LENGTH, LOCATE, POSITION, CHARACTER_LENGTH, DIFFERENCE (SOUNDEX distance)
• Implicit casts — many string functions accept a numeric argument and cast it to VARCHAR first (SUBSTR of a number). That VARCHAR has a CCSID; know that you are no longer in numeric type rules

CLOB in, CLOB out keeps you in the LOB family—do not SUBSTR a 10 MB CLOB into a VARCHAR(10) without a length that fits. VARCHAR has a maximum length (32 704 bytes of UTF-8 representation in many contexts); exceeding it is an error.

Mixed data input: SUBSTRING says if the first argument is mixed data, the result is mixed; otherwise SBCS (for character). Graphic stays graphic. Bit data stays bit data on VARCHAR of bit data.

Null behaviour

The rule to tattoo on your wrist: if any argument is null, the result is null for the ordinary string scalars (SUBSTR, SUBSTRING, CONCAT, LENGTH, UPPER, TRIM, LOCATE, …).

1
2
3
4
5
6
7
-- If MIDINIT is null, full name is null — not 'HAAS' with a gap
SELECT FIRSTNME || MIDINIT || LASTNAME
  FROM DSN8C10.EMP;
 
-- Treat null middle initial as empty
SELECT FIRSTNME || COALESCE(MIDINIT, '') || LASTNAME
  FROM DSN8C10.EMP;

• Null is not empty string — COALESCE or IFNULL (VALUE) when you want concatenation to survive
• LENGTH(NULL) is null, not zero. LENGTH of empty VARCHAR is 0
• LOCATE of a pattern in null is null, not 0. LOCATE returning 0 means “not found” in a non-null string
• Aggregates vs scalars — MIN(LASTNAME) ignores nulls; UPPER(LASTNAME) is null for a null last name. Do not mix the two mental models

Empty string '' is a real zero-length value. TRIM of all blanks can produce empty string, which is still NOT NULL if the column/expression is not null. That empty string is what LENGTH = 0 sees.

CCSID considerations

A CCSID identifies the encoding of a string: EBCDIC 37, UTF-8 1208, UTF-16 graphic 1200, and so on. String functions do not throw encodings away; they either preserve the input CCSID or assign one from encoding-scheme rules.

Preserve

SUBSTR, SUBSTRING, and similar slices: CCSID of the result is the CCSID of the first argument. You are cutting the same byte stream, not translating it.

Assign / convert

CHAR, VARCHAR, ASCII_STR, UNICODE_STR, concatenation of mixed schemes: Db2 applies encoding scheme and CCSID rules for strings. Unicode constants in SQL text, EBCDIC columns, and UTF-16 graphic columns can meet in one expression. The result CCSID is defined; if a character cannot be mapped, you get substitution characters or an error depending on the function and settings.

String units

IBM’s SUBSTRING example uses FIRSTNAME = Jürgen. SUBSTRING(..., 1, 2, CODEUNITS32) keeps two characters (Jü). A byte-oriented SUBSTR of length 2 on UTF-8 can split ü and return an invalid fragment. On EBCDIC SBCS English data, OCTETS and “characters” coincide and beginners never notice. On Unicode columns they do not.

Bit data

FOR BIT DATA and BINARY/VARBINARY are not text. HEX is the readable dump. Do not UPPER bit data expecting letters. VARCHAR of bit data remains bit data. Concatenating bit data with ordinary character data is a type/CCSID mistake—keep the families separate.

Graphic and UTF-16

LENGTH of graphic data is in double-byte characters. A UTF-16 supplementary character takes two DBCS code units and counts as two for LENGTH. CHARACTER_LENGTH with CODEUNITS32 is the “user perceived character” count when you need it.

1
2
3
4
5
6
SELECT LENGTH(FIRSTNME) AS LEN_BYTES_OR_CHARS,
       OCTET_LENGTH(FIRSTNME) AS LEN_OCTETS,
       LENGTH(HIREDATE) AS LEN_DATE_INTERNAL,  -- 4 for DATE
       LENGTH(CHAR(HIREDATE, EUR)) AS LEN_DATE_CHAR -- 10 for EUR
  FROM DSN8C10.EMP
 WHERE EMPNO = '000280';

LENGTH(DATE) is 4 (internal date length), not 10. LENGTH(CHAR(date, EUR)) is 10. That is a CCSID-and-representation lesson: functions see the type you pass, not the picture on the QMF screen.

How to choose a function

• Need characters not bytes — SUBSTRING / CHARACTER_LENGTH with CODEUNITS32, not SUBSTR / LENGTH on UTF-8
• Need a host-friendly CHAR — CHAR() with an explicit length and format
• Need to join pieces — CONCAT / || with COALESCE on nullable parts
• Need to compare case-insensitively — UPPER both sides, aware that case folding is CCSID-specific; some locales need more than UPPER
• Need hex for dumps — HEX, not a homemade TRANSLATE

Predicates like LIKE are not functions but they share CCSID and mixed-data issues. Keep pattern literals in the same encoding family as the column.

Explain It Like I'm Five

A string is a row of beads on a wire. Some beads are letters, and in Unicode one letter might be two beads glued together. SUBSTR counts beads (bytes). SUBSTRING ... CODEUNITS32 counts letters. CONCAT ties two wires together. TRIM pulls blank beads off the ends. LENGTH counts how many beads are left. If someone hands you “no wire” (null), you do not get an empty wire—you get “nothing,” and every toy that needed a wire also becomes nothing. The paint color on the beads is the CCSID: mixing a box of EBCDIC beads with Unicode beads means Db2 repaints some of them so they match.

Exercises

1. List four categories of string functions and one function in each category.
2. Write CONCAT (or ||) of FIRSTNME, a space, and LASTNAME so a null FIRSTNME still yields the last name.
3. When would SUBSTRING with CODEUNITS32 be safer than SUBSTR on a Unicode VARCHAR column?
4. What is LENGTH of a null VARCHAR versus LENGTH of an empty VARCHAR?
5. Why is LENGTH(HIREDATE) 4 while CHAR(HIREDATE, EUR) has length 10?