CONCAT Operator ‘||’ Vs. CONCAT Function – Are They Identical?

CONCAT Operator ||:

The operands of concatenation must be compatible strings. A binary string cannot be concatenated with a character string, including character strings that are defined as FOR BIT DATA . A distinct type that is based on a string type can be concatenated only if an appropriate user-defined function is created.

Both CONCAT and the vertical bars (||) represent the concatenation operator. Vertical bars (or the characters that must be used in place of vertical bars in some countries) can cause parsing errors in statements passed from one DBMS to another. The problem occurs if the statement undergoes character conversion with certain combinations of source and target. Thus, CONCAT is the preferable concatenation operator.

CONCAT Function:

The arguments must be compatible strings.

Either argument can also be a numeric data type. The numeric argument is implicitly cast to a VARCHAR data type.End of change. The result of the function is a string that consists of the first string followed by the second string.

The result can be null; if any argument is null, the result is the null value. The CONCAT function is identical to the CONCAT operator. 

Example: Using sample table DSN8A10.EMP, concatenate column FIRSTNME with column LASTNAME. Both columns are defined as varying-length character strings.
SELECT CONCAT(FIRSTNME, LASTNAME)
FROM DSN8A10.EMP;