SUBSTR and SUBSTRING functions in DB2

Why two substring functions exist

SUBSTR has been in Db2 for decades. COBOL programmers reach for it the way they reach for MOVE with a reference modification. Positions are counted in the natural units of the data type: bytes for character and binary strings, double-byte characters for graphic strings. The start argument is strict. If you ask for a start past the length attribute, Db2 rejects the call.

SUBSTRING arrived later to match the SQL standard and Unicode-aware processing. You can say “give me two characters” instead of “give me two bytes.” That difference is invisible for plain EBCDIC names such as SMITH and painful for names such as Jürgen, where ü is more than one byte in UTF-8.

Both functions live in schema SYSIBM. Qualify them only when function path resolution might pick a user-defined function with the same name.

The SUBSTR scalar function

Syntax:

1
SUBSTR(string-expression, start [, length])

Arguments:

• string-expression — the source. Character, graphic, or binary string. This is the value you are slicing.
• start — a large integer. Must be between 1 and the length attribute of the string (for VARCHAR that means the maximum length, not the actual length). 1 is the first character or byte.
• length — optional. A large integer greater than or equal to 0, and not greater than length-attribute − start + 1. The integer constant 0 is not allowed as length. Omit length to take the remainder of the string.

If the source is a fixed-length string and you omit length, Db2 uses LENGTH(string) − start + 1. If the source is varying-length and you omit length, Db2 uses the greater of zero and that same formula, so a start past the actual length of a short VARCHAR can yield an empty string rather than padding.

Result type of SUBSTR

The result can be null; a null argument produces a null result. The data type follows the source (character stays character, graphic stays graphic, binary stays binary). Length attribute rules that bite beginners:

• If length is an integer constant of 255 or less and the source is not a LOB, the result is a fixed-length string of that length.
• If length is omitted, the source is fixed-length, and start is an integer constant, the result is fixed-length with attribute LENGTH(string) − start + 1.
• In all other cases the result is varying-length. If length is an integer constant, that constant is the length attribute; otherwise the length attribute matches the source.

That CHAR-versus-VARCHAR distinction matters in COBOL host variables and in predicates. A CHAR(3) result is blank-padded. A VARCHAR(3) result of ABC has actual length 3 with no extra blanks.

SUBSTR examples

1
2
3
4
5
6
7
8
SELECT SUBSTR('DATABASE', 1, 4) AS FIRST4,   -- 'DATA'
       SUBSTR('DATABASE', 5)    AS REST     -- 'BASE'
  FROM SYSIBM.SYSDUMMY1;
 
SELECT EMPNO,
       SUBSTR(LASTNAME, 1, 3) AS NAME3
  FROM DSN8C10.EMP
 WHERE WORKDEPT = 'A00';

For a VARCHAR column NAME with value MCKNIGHT (eight characters), SUBSTR(NAME, 10) — start beyond the actual length but within the length attribute — yields an empty string, not an error. That surprises people who expected padding or SQLCODE −171. The start is still legal because the VARCHAR length attribute (the maximum) is large enough.

Mixed EBCDIC data is a special hazard. SUBSTR counts bytes, including shift-out (X'0E') and shift-in (X'0F') control characters. Slicing through the middle of a DBCS pair produces a string that is not well-formed mixed data. Prefer SUBSTRING with CODEUNITS32, or keep mixed columns out of byte-oriented SUBSTR until you know the layout.

The SUBSTRING scalar function

SUBSTRING has three type-specific forms:

1
2
3
4
5
6
7
8
-- Character
SUBSTRING(character-expression, start [, length] [, CODEUNITS16 | CODEUNITS32 | OCTETS])
 
-- Graphic
SUBSTRING(graphic-expression, start [, length] [, CODEUNITS16 | CODEUNITS32])
 
-- Binary
SUBSTRING(binary-expression, start [, length])

A numeric first argument is implicitly cast to VARCHAR. Start and length can also be values that Db2 can assign to INTEGER (including some string forms that cast through DECFLOAT(34)).

String unit keywords

• CODEUNITS32 — count Unicode UTF-32 characters. Best when you mean “human characters,” including supplementary characters as one unit.
• CODEUNITS16 — count UTF-16 code units. A supplementary character is two CODEUNITS16 units (a surrogate pair).
• OCTETS — count bytes. Same spirit as classic SUBSTR on character data. Illegal for graphic strings. CODEUNITS16 and CODEUNITS32 are illegal for FOR BIT DATA character strings.

Binary SUBSTRING always works in bytes and does not accept unit keywords.

Start and length are more forgiving

For SUBSTRING, start may be positive, negative, or zero. Length, if specified, must be greater than or equal to 0. If you specify a length larger than what remains in the string, Db2 uses the remaining length instead of failing. If the computed window does not overlap the string at all, you get a zero-length string.

1
2
3
4
5
-- C1 = 'ABCDEFG'
SUBSTRING(C1, -2, 2, OCTETS)  -- empty string (window is before the data)
SUBSTRING(C1, -2, 4, OCTETS)  -- 'A'   (one byte overlaps the start)
SUBSTRING(C1, -2, OCTETS)     -- 'ABCDEFG'
SUBSTRING(C1,  0, 1, OCTETS)  -- empty string

IBM’s Unicode example with FIRSTNAME = Jürgen (UTF-8) shows why units matter:

1
2
3
4
SUBSTRING(FIRSTNAME, 1, 2, CODEUNITS32)  -- 'Jü'
SUBSTRING(FIRSTNAME, 1, 2, CODEUNITS16)  -- 'Jü'
SUBSTRING(FIRSTNAME, 1, 2, OCTETS)       -- truncated: 'J' plus a blank
SUBSTRING(FIRSTNAME, 8, CODEUNITS16)     -- empty string

The result type of SUBSTRING is typically varying-length: VARCHAR from CHAR/VARCHAR, CLOB from CLOB, VARGRAPHIC from GRAPHIC/VARGRAPHIC, VARBINARY from BINARY/VARBINARY, BLOB from BLOB. The length attribute of the result matches the first argument (with extra calculation when CODEUNITS16 or CODEUNITS32 is specified). CCSID of a character or graphic result matches the source.

SUBSTR versus SUBSTRING at a glance

Practical guidance for z/OS shops:

• Legacy COBOL and EBCDIC CHAR/VARCHAR — SUBSTR is the function everyone already knows. Keep using it for fixed codes and byte layouts.
• Unicode, names, and mixed data — SUBSTRING with CODEUNITS32 so you do not split a character.
• Need a CHAR(n) result for a host variable — SUBSTR with a constant length of n (n ≤ 255) is the usual trick.
• Need “safe” slices that become empty instead of failing — SUBSTRING.

Nulls, empty strings, and predicates

A null source, start, or length makes the result null. An empty string is not null. If you filter WHERE SUBSTR(COL, 1, 1) = 'A', rows where COL is null do not qualify — three-valued logic still applies. If you meant “treat null like blank,” wrap with COALESCE first.

1
2
3
SELECT EMPNO, LASTNAME
  FROM DSN8C10.EMP
 WHERE SUBSTR(COALESCE(LASTNAME, ' '), 1, 1) BETWEEN 'A' AND 'M';

Index matching: a SUBSTR on a column is an expression. Stage 1 index matching on that column often disappears unless you have an index on the expression or a matching generated column. For a prefix search, LIKE 'ABC%' can be a better optimizer friend than SUBSTR(COL,1,3) = 'ABC' on a VARCHAR, depending on statistics and index design. Measure with EXPLAIN; do not guess.

COBOL host variables

When SUBSTR returns CHAR(n), the COBOL receiving field is typically PIC X(n). When the result is VARCHAR, you need a varying structure (length halfword plus data) from DCLGEN or a VARCHAR host variable. Mixing them causes truncated data or SQLCODE −433 / −302 style problems. Always match the result type, not the source type — SUBSTR of a VARCHAR with a constant length 5 is CHAR(5), not VARCHAR.

1
2
3
4
5
6
7
      *    Assume LAST3 is PIC X(3)
           EXEC SQL
                SELECT SUBSTR(LASTNAME, 1, 3)
                  INTO :LAST3
                  FROM DSN8C10.EMP
                 WHERE EMPNO = :EMPNO
           END-EXEC.

Common mistakes

• Treating start as 0-based (C habit). In DB2, 1 is the first character.
• Passing length 0 as a literal to SUBSTR — not allowed as a constant.
• Using SUBSTR on UTF-8 names and wondering why the second “character” is garbage — switch to SUBSTRING with CODEUNITS32.
• Assuming SUBSTR(VARCHAR_COL, 1, 10) always returns ten characters. Actual length can be shorter; CHAR padding only applies when the result type is fixed-length.
• Forgetting that a null argument yields null, so the substring never equals a literal.

Explain It Like I'm Five

Imagine a row of letter tiles. SUBSTR says “start at tile number 3 and pick 4 tiles.” Tile number 1 is the first tile — nobody starts counting at zero. If you ask for a tile number that is not on the rack, SUBSTR complains. SUBSTRING is a kinder friend: if you point a little to the left of the rack, it just hands you whatever tiles actually exist, or an empty handful. SUBSTRING can also count “letters” instead of “inches of wood,” which matters when one letter is painted with two strokes and takes extra space.

Exercises

1. Write SUBSTR to return the first five characters of LASTNAME from DSN8C10.EMP.
2. Explain whether SUBSTR(VARCHAR_COL, 1, 3) returns CHAR(3) or VARCHAR, and why that matters to a COBOL PIC X(3) host variable.
3. Given UTF-8 value Jürgen, predict SUBSTRING(name, 1, 2, OCTETS) versus SUBSTRING(name, 1, 2, CODEUNITS32).
4. Show a SUBSTRING call with a negative start that still returns the first character of ABCDEFG (use the IBM OCTETS example as a model).
5. Rewrite a predicate SUBSTR(ACCT,1,3) = '101' as a LIKE predicate and discuss when each form is clearer.