DB2 REPLACE, TRANSLATE, INSERT, REPEAT and SPACE

How these five functions fit together

All five are scalar functions in schema SYSIBM. You can use them anywhere an expression is legal: SELECT lists, WHERE predicates, SET clauses, VALUES, and CHECK constraints that allow functions. They are not aggregate functions. Each call looks at one set of argument values and returns one string (or null).

Null behaviour is consistent: if any argument is null, the result is null. That matters in COBOL host variables with null indicators and in outer joins where a missing name must not silently become an empty string. Empty strings and nulls are different; REPLACE of a non-null source with a found token still yields a non-null string, even if the replacement is zero length.

Encoding still applies. Character arguments keep an encoding scheme and CCSID. Mixing EBCDIC application data with Unicode columns forces conversion before the function runs. Binary strings are allowed for some of these functions (REPLACE and INSERT) but not for classic SBCS-oriented TRANSLATE maps. When a result would exceed the maximum length of the return type (typically VARCHAR up to 32704 bytes in these contexts), Db2 raises an error instead of silently truncating.

REPLACE — swap every occurrence of a search string

REPLACE searches source-string for every occurrence of search-string and substitutes replace-string. The search is not a regular expression and is not case-insensitive. It is a straight byte/character sequence match.

1
REPLACE(source-string, search-string, replace-string)

• source-string — the text to scan. Built-in character, graphic, or binary string that is not a LOB. It must not be an empty string.
• search-string — the sequence to remove. Same type family; must not be empty. Length limits apply (commonly 4000 bytes for character/binary, 2000 for graphic, with a higher Unicode DBCS limit).
• replace-string — what to put in each hole. An empty replace-string deletes the search text. A null replace-string makes the whole result null.

If search-string never appears, Db2 returns source-string unchanged. Occurrences do not overlap in the usual left-to-right scan: after a replacement, scanning continues after the inserted text, so a replace-string that contains search-string is not immediately replaced again in that same pass.

1
2
3
4
SELECT REPLACE('DINING', 'N', 'VID') AS STEP1,
       REPLACE('MAINFRAME INC', 'INC', 'LTD') AS STEP2,
       REPLACE('A--B--C', '--', '') AS STRIP_DASHES
  FROM SYSIBM.SYSDUMMY1;

STEP1 is the classic IBM example: each N becomes VID, so DINING becomes DIVIDIVIDG. STEP2 shows a token swap for a legal name. STRIP_DASHES shows deletion: replace-string is empty, so double dashes vanish and A--B--C becomes ABC.

Result type follows the source (typically VARCHAR). The actual length is the source length plus occurrences times (length of replace minus length of search). Expanding 'N' to 'VID' in a long column can overflow VARCHAR; test with realistic data, not just a five-letter sample.

REPLACE compared with overlay-style edits

REPLACE does not take a start position. It always means “every match.” If you only want to change characters 4 through 6, use INSERT (or SUBSTR concatenation) instead. If you want to change every letter A into B regardless of neighbours, TRANSLATE is cheaper to reason about than REPLACE(col, 'A', 'B'), though both can work for a one-character search.

TRANSLATE — character map, optional uppercase shortcut

TRANSLATE returns a string in which selected characters have been mapped to other characters. Think of a pair of typewriter bars: from-string is the keys you press, to-string is the glyphs that print.

1
2
3
TRANSLATE(expression)
TRANSLATE(expression, to-string, from-string)
TRANSLATE(expression, to-string, from-string, pad-character)

• One-argument form — lowercase single-byte letters become uppercase. Useful as a quick fold, but UPPER/UCASE is the clearer name when case is the only goal, especially with locales.
• to-string — the replacement characters, in the same order as from-string. Extra to-string characters beyond the from-string length are ignored.
• from-string — characters to look up. Duplicate characters: only the first occurrence counts; later duplicates are ignored.
• pad-character — a single character used when to-string is shorter than from-string. Default is a blank. That default is how people “delete” characters: map them to blank, or map them to a pad you later STRIP.

1
2
3
4
5
SELECT TRANSLATE('Hello') AS UPPERISH,
       TRANSLATE('ABC-123', '999', '123') AS MASK_DIGITS,
       TRANSLATE('A-B-C', '', '-') AS DASH_TO_BLANK,
       TRANSLATE('banana', 'X', 'an', '*') AS MAP_WITH_PAD
  FROM SYSIBM.SYSDUMMY1;

MASK_DIGITS turns 1, 2, and 3 into 9, so ABC-123 becomes ABC-999. DASH_TO_BLANK uses an empty to-string, so each dash maps to the default pad (blank): A-B-C becomes A B C. MAP_WITH_PAD shows a short to-string: 'a' maps to X, and 'n' has no matching to-character so it becomes the pad '*', producing bX*X*X.

TRANSLATE does not search for the sequence 'an'. It never treats from-string as a substring. That is the REPLACE versus TRANSLATE exam question. REPLACE('banana', 'an', 'X') replaces the two-character token an. TRANSLATE('banana', 'X', 'an') maps the letter a and the letter n independently.

Mixed SBCS/DBCS translation has extra rules: you cannot map a single-byte character to a multi-byte character (or the reverse) in the same from/to pair. Pad must be a valid single character for the string type. Graphic strings use graphic from/to/pad. If you need Unicode-aware case folding, prefer UPPER/LOWER with a locale rather than assuming one-argument TRANSLATE understands every alphabet.

INSERT — splice at a position (not INSERT INTO)

The INSERT scalar function deletes length string units from source-string beginning at start, then inserts insert-string at that same start position. It is an in-string splice. It does not write rows.

1
2
3
4
INSERT(source-string, start, length, insert-string)
INSERT(source-string, start, length, insert-string, CODEUNITS16)
INSERT(source-string, start, length, insert-string, CODEUNITS32)
INSERT(source-string, start, length, insert-string, OCTETS)

• start — 1-based position of the first unit to delete (and the place the new text begins). Values less than 1 are invalid.
• length — how many units to delete. Zero means insert without deleting. Negative length is invalid.
• insert-string — the text to splice in. An empty insert-string with a positive length is a delete-only edit.
• CODEUNITS16 / CODEUNITS32 / OCTETS — how start and length are counted for Unicode data. OCTETS counts bytes. CODEUNITS16 counts UTF-16 units. CODEUNITS32 counts Unicode scalar values. Omit them for ordinary EBCDIC SBCS columns; the default string unit matches the string type.

1
2
3
4
SELECT INSERT('ABCDE', 3, 2, 'XYZ') AS SPLICE,
       INSERT('ABCDE', 3, 0, 'XYZ') AS INJECT,
       INSERT('ABCDE', 3, 2, '') AS DELETE_ONLY
  FROM SYSIBM.SYSDUMMY1;

SPLICE starts at C, deletes two characters (CD), and puts XYZ in the hole: ABXYZE. INJECT deletes nothing (length 0) so XYZ is injected before C: ABXYZCDE. DELETE_ONLY removes CD and puts nothing back: ABE.

If start is just past the end of the source, the insert-string is effectively appended (after any required padding to reach start, using blanks for character data or hex zeros for binary). If start + length runs past the end, Db2 deletes only through the last existing unit. Always validate start against LENGTH(source) in application code when the position comes from a host variable.

COBOL programmers meet two different INSERT words in the same program: EXEC SQL INSERT INTO for DML, and INSERT(col, 1, 0, :PREFIX) in a SELECT or SET. Read the parentheses. Function INSERT always has a source string as the first argument. Statement INSERT always has INTO.

REPEAT — copy a string n times

REPEAT(expression, integer) returns expression concatenated with itself integer times. The integer is the repeat count, not a byte length.

1
2
3
4
5
SELECT REPEAT('*', 5) AS STARS,
       REPEAT('AB', 3) AS ABABAB,
       REPEAT(LASTNAME, 2)
  FROM DSN8C10.EMP
 FETCH FIRST 3 ROWS ONLY;

• integer 0 — empty string (zero copies)
• integer 1 — the original string once
• integer > 1 — that many copies with no separator
• negative integer — error
• null count or null string — null result

REPEAT is the building block for simple padding when LPAD/RPAD are not the shape you want, and for generating test data. Watch the result length: REPEAT(CHAR(100), 400) will overflow. Compute LENGTH(expression) * count before using a large host-variable count in production SQL.

SPACE — a string of blanks

SPACE(numeric-expression) returns a VARCHAR of that many blank characters. The numeric argument is truncated to integer if it is decimal. SPACE(0) is empty. A negative argument is invalid. Null in, null out.

1
2
3
SELECT 'A' CONCAT SPACE(4) CONCAT 'B' AS A____B,
       REPEAT(' ', 4) AS SAME_IDEA
  FROM SYSIBM.SYSDUMMY1;

SPACE(4) and REPEAT(' ', 4) both yield four blanks. SPACE states the intent more clearly in report SQL and in CONCAT expressions that rebuild fixed-width layouts (for example recreating an 80-byte print line from VARCHAR pieces). Do not use SPACE to mean “null.” Blanks are real characters; they compare equal to other blanks and they are not UNKNOWN in predicates.

Choosing the right edit

Worked comparison on the same source 'A-123-X':

• REPLACE(src, '-', '/') → A/123/X — every dash substring (here one character) becomes slash
• TRANSLATE(src, '/', '-') → same result for a one-character map, but TRANSLATE could also map 1,2,3 in the same call
• INSERT(src, 3, 3, '999') → A-999-X — only the slice at positions 3–5 changes
• REPLACE(src, '123', '999') → A-999-X — same output here, but it would also change 123 anywhere else in a longer string

In UPDATE statements, these functions are expressions on the right of SET. They do not lock extra rows by themselves; locking follows the UPDATE. In predicates, wrapping a column in REPLACE or TRANSLATE usually prevents index matching on that column unless you have an expression index or generated column that matches. Prefer storing a canonical form (digits-only phone, upper name) in a real column if you filter on it all day.

Explain It Like I'm Five

Imagine a row of alphabet fridge magnets. REPLACE is “every time you see the word CAT, swap in DOG.” TRANSLATE is “every letter C becomes a K, every letter A becomes an O” — one magnet at a time, using a cheat sheet. INSERT is “take out three magnets starting at slot 4 and push these new magnets into that gap.” REPEAT is “copy this magnet word five times in a row.” SPACE is “put this many empty magnet slots (blanks) here.” None of these is the same as walking to the fridge and sticking a whole new row of magnets on a different door — that other job is the INSERT INTO statement that adds table rows.

Exercises

1. Predict REPLACE('MISSISSIPPI', 'SS', 'X') and then run it on SYSIBM.SYSDUMMY1. Explain why the result is not MIXIXIPPI or MIXIXXIPPI without drawing the left-to-right scan.
2. Use TRANSLATE to keep only digits from a phone column that may contain dashes, parentheses, and spaces. Decide whether the pad should be blank plus a later STRIP, or whether a different technique (REPLACE nested, or a generated column) is cleaner.
3. Write INSERT so that the string ACCT-000019 becomes ACCT-XX0019 (replace the first two zeros after the dash without using REPLACE).
4. Build a 10-character filler of asterisks two ways: REPEAT and a combination of SPACE plus TRANSLATE. Which is easier to read?
5. Explain to a teammate why INSERT INTO EMP ... and SELECT INSERT(LASTNAME, 1, 0, 'X') are unrelated operations that share a verb.