DB2 CONCAT (Concatenate) Function

The DB2 CONCAT function will combine two separate expressions to form a single string expression. It can leverage database fields, or explicitly defined strings as one or both expression when concatenating the values together.

The syntax for the CONCAT function is shown here:

-- Using Database Fields
SELECT CONCAT({field_1}, {field_2}) FROM {table_name};

-- Using String Expressions
SELECT CONCAT('{string_1}', '{string_2}') FROM {table_name};

-- Using Database Fields and String Expressions
SELECT CONCAT({field_1}, '{string_2}') FROM {table_name};

To show an example statement in use:

SELECT CONCAT(MFNAME, MFPARTNUMBER) FROM CATENTRY;

The SQL statement above will take and concatenate the values from the MFNAME and MFPARTNUMBER columns in the CATENTRY table.

If we want to add an explicit string (or space) between the two columns, we could leverage the following SQL statement to do so. This query will add a blank space between the two defined expressions.

SELECT CONCAT(CONCAT(MFNAME, ' '), MFPARTNUMBER) FROM CATENTRY;

NOTE: There is a shortcut notation available; however, I’ve found that if DB2 is not configured to enable it you will receive errors should you attempt to use it.

To leverage the CONCAT function shortcut, you’ll leverage a ‘||’ (double pipe) notation, and will result in an SQL query with the following syntax and will produce the same result as the second example query above:

SELECT MFNAME || ' ' || MFPARTNUMBER FROM CATENTRY;

If you want to read the IBM DB2 documentation on the CONCAT scalar function, please visit the DB2 v9.7 Information Center: CONCAT scalar function.