CONCAT and string concatenation in DB2

The CONCAT scalar function

Syntax:

1
CONCAT(expression1, expression2)

The schema is SYSIBM. The function returns a string that consists of the first argument followed by the second. The two arguments must be compatible strings. IBM documents the scalar function as identical to the CONCAT operator.

1
2
3
4
SELECT CONCAT(FIRSTNME, LASTNAME)
  FROM DSN8C10.EMP
 WHERE EMPNO = '000010';
-- Sample result: CHRISTINEHAAS  (no space)

Notice there is no automatic space. If you want a blank between first and last name, concatenate a literal blank as its own piece.

1
2
3
4
SELECT CONCAT(CONCAT(FIRSTNME, ' '), LASTNAME) AS FULLNAME
  FROM DSN8C10.EMP
 WHERE EMPNO = '000010';
-- CHRISTINE HAAS

The CONCAT and || operators

In expressions, concatenation is an operator alongside +, −, *, and /. You can write:

1
2
3
4
5
SELECT FIRSTNME CONCAT ' ' CONCAT LASTNAME AS FULLNAME
  FROM DSN8C10.EMP;
 
SELECT FIRSTNME || ' ' || LASTNAME AS FULLNAME
  FROM DSN8C10.EMP;

Operator precedence: unary plus and minus bind first, then multiplication, division, and concatenation, then addition and subtraction. Same-level operators evaluate left to right. Parentheses remove all doubt when you mix numbers and strings through CHAR conversions.

Why CONCAT is safer than ||

Vertical bars (or the characters that replace them in some national EBCDIC pages) can cause parse errors when a statement is converted between CCSIDs. Db2 for z/OS also treats certain code-point pairs (including X'4F4F', and on SBCS systems X'BBBB' and X'5A5A') as concatenation. X'BBBB' is a pair of right brackets in some code pages and is not concatenation inside an array-index expression. IBM’s SQL Reference therefore says CONCAT is the preferable concatenation operator for portable SQL. Shop standards on z/OS almost always agree: write CONCAT in programs that might be bound on another subsystem or copied into a different code page.

Null behaviour

If either operand can be null, the result can be null. If either is null, the result is the null value. Concatenation does not skip nulls the way some reporting tools do.

1
2
3
4
5
6
7
8
9
-- If MIDINIT is NULL, FULLNAME is NULL — not 'CHRISTINE HAAS'
SELECT FIRSTNME CONCAT ' ' CONCAT MIDINIT CONCAT ' ' CONCAT LASTNAME
  FROM DSN8C10.EMP;
 
-- Treat missing middle initial as empty
SELECT FIRSTNME CONCAT ' '
     CONCAT COALESCE(MIDINIT CONCAT ' ', '')
     CONCAT LASTNAME
  FROM DSN8C10.EMP;

An empty string is not null. CONCAT('A', '') is 'A'. CONCAT('A', NULL) is null. That distinction is the same three-valued logic you already use in WHERE clauses.

Compatible types

Operands must be compatible strings:

• Character with character — usual case (CHAR, VARCHAR, CLOB, and compatible encodings after conversion).
• Graphic with graphic — GRAPHIC, VARGRAPHIC, DBCLOB. Character and graphic can be concatenated when conversion rules allow (Unicode path converts character to graphic / UTF-16 as documented).
• Binary with binary — BINARY, VARBINARY, BLOB. A binary string cannot be concatenated with a character string, including character strings defined as FOR BIT DATA.
• Distinct types — even if based on VARCHAR, they are not built-in strings. Create a sourced user-defined function on CONCAT, or overload "||".

1
2
3
CREATE FUNCTION ATTACH (TITLE, TITLE_DESCRIPTION)
  RETURNS VARCHAR(50)
  SOURCE CONCAT (VARCHAR(), VARCHAR());

Result data type and length

The length of the result is the sum of the lengths of the operands (with a two-byte reduction if redundant EBCDIC mixed shift codes are eliminated — see below). Combined CHAR operands shorter than 256 stay CHAR; longer combinations and any VARCHAR operand produce VARCHAR, capped at 32764 after conversion adjustments. LOB operands promote to CLOB, DBCLOB, or BLOB with very large caps (2G bytes or 1G double-byte characters).

If conversion of an operand is required, Db2 may inflate that operand’s length in the formula (IBM notes x' = 3x when conversion of the first operand is required). That is why a “short” concatenation can still be typed as a long VARCHAR — the length attribute must be large enough after CCSID conversion.

Mixed data: if either CHAR operand contains mixed data, the result is VARCHAR, not CHAR. If either operand is BIT data, the result is BIT data. Field procedures on a column apply to the decoded value; the concatenation result does not inherit the field procedure.

Redundant shift codes in EBCDIC mixed data

When both strings are EBCDIC mixed data and the first ends with shift-in (X'0F') while the second begins with shift-out (X'0E'), those two control characters are removed so you do not keep a useless SI/SO pair in the middle. The result length is then two bytes less than the naive sum. Beginners who HEX() the result and count bytes sometimes think Db2 “lost” data; it discarded control characters only.

CCSID of the result

The CCSID follows the rules for character conversion in concatenations (the same family of rules used in set operations). Consequences you will actually see:

• BIT data operand → BIT data result.
• SBCS compared or concatenated with mixed data: in Unicode, SBCS is converted to MIXED. On EBCDIC, the MIXED DATA installation option (DSNTIPF) decides. If MIXED DATA is NO and mixed data cannot convert to pure SBCS, you get an error.

Comparing a Unicode column to an EBCDIC literal you concatenated in the application can force conversions in both the CONCAT and the predicate. When a report “looks almost right,” dump HEX() of both sides before rewriting business logic.

Parameter markers and nested CONCAT

One operand can be a parameter marker. Its type and length are taken from the other operand, with special length formulas when that operand is a string (see the SQL Reference table for untyped markers). Nested concatenation is evaluated with operator order, so the derived type of ? CONCAT A CONCAT B depends on left-to-right grouping. Write parentheses when you mix markers with literals of different lengths.

No operand of concatenation can be a distinct type without a supporting UDF, as noted above.

Building names, paths, and messages

Everyday patterns:

1
2
3
4
5
6
7
8
9
10
11
-- Fixed-width COBOL-style account key
SELECT CONCAT(BRANCH, CONCAT(TYPE, ACCT)) AS ACCT_KEY
  FROM ACCOUNTS;
 
-- Human-readable line
SELECT LASTNAME CONCAT ', ' CONCAT FIRSTNME
  FROM DSN8C10.EMP;
 
-- Cast numbers before concatenating
SELECT 'EMP#' CONCAT EMPNO CONCAT ' hired ' CONCAT CHAR(HIREDATE, ISO)
  FROM DSN8C10.EMP;

In application programs, prefer host variables over building dynamic SQL by concatenating user input. String concatenation of unchecked text into a statement is how SQL injection happens. CONCAT in a select list or SET clause is fine; CONCAT into the text of PREPARE is the dangerous one.

CONCAT versus other “join text” tools

• LISTAGG — concatenates values across rows in a group, with a separator. CONCAT only joins expressions on the same row.
• XMLCONCAT — XML values, not ordinary strings.
• || in other DBMS products — Oracle and PostgreSQL also use ||; Db2 LUW does too. The z/OS CCSID warning is the extra reason this platform prefers the CONCAT keyword.

Explain It Like I'm Five

Concatenation is taping two paper strips into one longer strip. CONCAT is the word for the tape. The two-bar symbol || is the same tape drawn as a picture, but if you photocopy the picture on a funny copier the bars can turn into other marks and the teacher cannot read the instruction. The word CONCAT still says “tape” after photocopying. If one strip is missing (NULL), you do not get a half-sign — you get nothing you can hang on the wall. Empty paper (the empty string) is still paper, so taping a blank scrap onto “HELLO” still leaves HELLO.

Exercises

1. Write CONCAT so FIRSTNME and LASTNAME appear with a single space, using the sample EMP table.
2. Predict the result of CONCAT('A', CAST(NULL AS CHAR(1))) and of CONCAT('A', '').
3. Explain why CHAR(200) CONCAT CHAR(200) is not CHAR(400).
4. Rewrite FIRSTNME || LASTNAME using the CONCAT keyword and state one reason a z/OS shop might require that form in code review.
5. Show how to concatenate a DATE column into a message using CHAR or VARCHAR so the format is explicit.