DB2 - SQL Concatenating Fields

How to concatenate two columns in db2 query?

The DB2 CONCAT function will combine two separate expressions to form a single string expression.

You can also combine two seperate expression to form a single string expression using ‘||’ (double pipe) notation.

Concatenation: It is joining values together (by appending them to each other) to form a single long value. In SQL SELECT statements, you can concatenate columns by using a special operator “||” or using CONCAT function.

Syntax 1: Using Database Fields

SELECT CONCAT(field_1, field_2) FROM table_name;
or
SELECT field_1 || field_2 FROM table_name;

Syntax 2: Using String Expressions

SELECT CONCAT(string_1, string_2) FROM table_name;
or
SELECT string_1 || string_2 FROM table_name;

Syntax 3: Using Database Fields and String Expressions

SELECT CONCAT(field_1, string_2) FROM table_name;
or
SELECT field_1 || string_2 FROM table_name;

DB2 Database:

Below is a selection from the "Product" table in the DB2 database.

Example 1: Using || Operator

SELECT ProductName || ' - ' || Category AS ProductDesc
  FROM Product;

The result of this query is as follows.

      ProductDesc
Mouse         - Accessories
Harddrive     - Components
Keyboard      - Accessories
RAM           - Components
Honda         - Bikes
PEN           - Stationary
Cddrive       - Accessories
Speaker       - Accessories

Example 2: Using CONCAT Function

SELECT
    CONCAT('IBM','MAINFRAMER') AS Result
FROM
    SYSIBM.SYSDUMMY1;

Here is the output:

Result
------
IBMMAINFRAMER

Example 3: Using CONCAT Function

SELECT CONCAT(CONCAT('IBMMAINFRAMER',' '),'TUTORIALS') AS Result
  FROM SYSIBM.SYSDUMMY1;

Here is the output:

Result
------
IBMMAINFRAMER TUTORIALS

If you have any doubts or queries related to this chapter, get them clarified from our Mainframe experts on ibmmainframer Community!