TRIM, STRIP, LTRIM, RTRIM, LPAD and RPAD in DB2

TRIM

The TRIM function removes bytes from the beginning, the end, or both ends of a string. Schema is SYSIBM.

1
2
TRIM([ BOTH | B | LEADING | L | TRAILING | T ] [trim-constant] FROM string-expression)
TRIM(string-expression)   -- both ends, default strip character

Arguments:

• BOTH / LEADING / TRAILING (or B / L / T) — which end to peel. If omitted, TRIM uses BOTH.
• trim-constant — a single character (SBCS or, for graphic data, a single DBCS character; for binary, a single-byte binary constant). If omitted, the default is a blank for character data (SBCS blank), a DBCS/UCS blank for graphic, or X'00' for binary.
• string-expression — character, graphic, or binary string that is not a LOB. Numeric values are cast to character (VARCHAR) first. Must not be a LOB.

1
2
3
4
5
6
7
8
9
SELECT TRIM(:HELLO),
       TRIM(TRAILING FROM :HELLO)
  FROM SYSIBM.SYSDUMMY1;
 
SELECT TRIM(BOTH 'x' FROM 'xxHELLOxx') AS T
  FROM SYSIBM.SYSDUMMY1;   -- HELLO
 
SELECT TRIM(L '0' FROM :BALANCE)
  FROM SYSIBM.SYSDUMMY1;   -- strip leading zeros

TRIM only walks inward from the chosen end(s). It stops at the first character that is not the strip character. It will not delete an x in the middle of HExLLO.

STRIP

STRIP is the same idea with argument order that looks more like a function call than the SQL-standard TRIM keyword list. IBM documents STRIP as similar to TRIM.

1
STRIP(string-expression [, BOTH | B | LEADING | L | TRAILING | T [, trim-constant]])

Defaults: BOTH, and a blank (or the binary/graphic default) if you omit the strip character. If string-expression is EBCDIC mixed data, the string must contain valid mixed data. Compatibility note: older DB2 9 trim behaviour can still be requested with subsystem parameter BIF_COMPATIBILITY = V9_TRIM for LTRIM, RTRIM, and STRIP. New code should use current TRIM/STRIP semantics and valid mixed strings.

1
2
3
4
SELECT STRIP(LASTNAME),
       STRIP(LASTNAME, T),
       STRIP(LASTNAME, L, '*')
  FROM DSN8C10.EMP;

LTRIM

LTRIM removes characters from the beginning of a string.

1
LTRIM(string-expression [, trim-expression])

• One argument — remove leading blanks (same idea as STRIP with LEADING).
• Two arguments — remove every character that appears in trim-expression from the left, comparing binary representations (byte-by-byte if FOR BIT DATA). The trim argument is a set of characters, not a single required character.

Numeric arguments are implicitly cast to VARCHAR. The result is VARCHAR, VARGRAPHIC, or VARBINARY to match the source. If every character is removed, you get an empty string (length zero), not null — unless an input was null.

1
2
3
4
SELECT LTRIM('   ABC')               AS BLANKS,   -- 'ABC'
       LTRIM('00123DEF', '0')        AS ZEROS,    -- '123DEF'
       LTRIM('xyzHELLO', 'xyz')      AS SET_TRIM  -- 'HELLO'
  FROM SYSIBM.SYSDUMMY1;

RTRIM

RTRIM is the right-hand twin. It removes bytes from the end of the string based on a trim expression.

1
RTRIM(string-expression [, trim-expression])

With one argument, trailing blanks disappear — the function you want on almost every CHAR column before concatenation. With two arguments, any character in trim-expression is stripped from the right until a non-member character is found.

1
2
3
4
5
SELECT RTRIM('123DEFG123', '321') FROM SYSIBM.SYSDUMMY1;
-- '123DEFG'  (trailing 1, 2, 3 characters removed)
 
SELECT RTRIM(FIRSTNME) CONCAT ' ' CONCAT RTRIM(LASTNAME) AS FULLNAME
  FROM DSN8C10.EMP;

CHAR(12) FIRSTNME values are blank-padded. Without RTRIM, CONCAT keeps those blanks and your “full name” has a canyon of spaces in the middle. VARCHAR columns only need RTRIM if trailing blanks were actually stored.

LPAD

LPAD returns a string made of the source padded on the left with a pad string (or blanks). Leading and trailing blanks in the source are significant — LPAD does not trim first.

1
LPAD(string-expression, integer [, pad])

• string-expression — built-in string that is not a LOB.
• integer — length of the result. Zero or a positive integer, at most 32704 for character or binary, 16352 for graphic.
• pad — string used to fill. Default is a blank (type-appropriate). If pad is omitted or you need a zero pad, pass '0' explicitly.

Padding occurs only if the actual length of the source is less than integer and pad is not empty. If integer is 0, the result is the empty string. If integer is less than the actual source length, the result is truncated to integer (from the left remaining / source kept from the left in the usual LPAD sense: the value is shortened to fit). The result is a varying-length string with length attribute equal to integer (or 1 when integer is 0). Source and pad must be compatible; pad is converted to the CCSID of the source when needed. FOR BIT DATA skips character conversion.

1
2
3
4
SELECT LPAD(EMPNO, 10, '0') AS EMP10,     -- 000010 if EMPNO is 000010 already; pads if shorter
       LPAD(RTRIM(LASTNAME), 12, '*') AS STAR_NAME
  FROM DSN8C10.EMP
 WHERE EMPNO = '000010';

RPAD

RPAD pads on the right. Same integer and pad rules as LPAD. Use RPAD when you need a fixed display width in a VARCHAR world, or when you are rebuilding a fixed-width extract without switching the column to CHAR.

1
2
3
4
RPAD(string-expression, integer [, pad])
 
SELECT RPAD(RTRIM(LASTNAME), 20, ' ') AS LAST20
  FROM DSN8C10.EMP;

CHAR already pads with blanks to the declared length, so RPAD on a CHAR column is often redundant unless you first RTRIM and then pad to a different width.

Choosing the right function

• Peel blanks from both ends — TRIM(expr) or STRIP(expr).
• Peel blanks from the right of CHAR — RTRIM(expr) before CONCAT or display.
• Peel leading zeros — TRIM(LEADING '0' FROM expr) or LTRIM(expr, '0').
• Peel a set of junk characters from one end — LTRIM/RTRIM with a two-argument trim-expression.
• Force a width with a fill character — LPAD or RPAD.
• Standard SQL in portable text — prefer TRIM over STRIP.

Nulls, empty strings, and mixed data

A null argument yields a null result. Trimming all characters yields an empty string, which is not null. Do not write WHERE TRIM(COL) IS NULL expecting to find blank CHAR columns; those become empty strings (or remain a string of other characters). Use WHERE TRIM(COL) = '' or WHERE COL IS NULL as two separate tests.

EBCDIC mixed strings passed to LTRIM, RTRIM, or STRIP must be valid mixed data. Invalid sequences can raise SQLCODE −171 at modern APPLCOMPAT levels. Empty SI/SO pairs next to the trim character can be trimmed away under current rules. If an old program depends on DB2 9 trim quirks, that is a BIF_COMPATIBILITY conversation with your DBA, not something to copy into new SQL.

COBOL pattern

Host CHAR fields are PIC X(n) and already look padded. When you SELECT RTRIM(LASTNAME) into PIC X(15), Db2 still assigns a character string that may be shorter; the rest of the host field is typically blank-padded on assignment. When you SELECT into a VARCHAR host structure, you see the actual length after the trim. Match DCLGEN types to the function result (VARCHAR family for TRIM/LTRIM/RTRIM/LPAD/RPAD results).

1
2
3
4
5
6
           EXEC SQL
                SELECT RTRIM(LASTNAME)
                  INTO :LASTNAME-V
                  FROM DSN8C10.EMP
                 WHERE EMPNO = :EMPNO
           END-EXEC.

Explain It Like I'm Five

Imagine a name written on a sticker with extra empty squares on the left and right. TRIM and STRIP peel empty squares off both sides, or only the left, or only the right. LTRIM only peels the left; RTRIM only peels the right. If you hand them a list of junk letters, they keep peeling those junk letters from that side like picking raisins off the crust of a cookie — they stop when they hit a real ingredient. LPAD and RPAD do the opposite: they add extra squares (stars, zeros, or blanks) so the sticker is exactly as wide as you asked. If the name is already too wide, they cut it to fit.

Exercises

1. Write TRIM to remove leading zeros from a CHAR(8) column that holds an account number.
2. Show RTRIM of FIRSTNME and LASTNAME concatenated with a single space, and explain what happens without RTRIM on CHAR columns.
3. Use LTRIM with a two-argument form to remove any combination of leading asterisks and blanks.
4. Pad EMPNO on the left with zeros to length 10 using LPAD. Then explain what happens if someone passes a 12-character source.
5. Rewrite STRIP(COL, T, '*') as a TRIM expression.