Db2 CONCAT

Summary: in this tutorial, you will learn how to use the Db2 CONCAT() function to concatenate two strings into a single string.

Db2 CONCAT() function overview

The CONCAT() function accepts two string arguments and concatenates these strings into a single string.

Here is the syntax of the CONCAT() function:

CONCAT(s1, s2);
Code language: SQL (Structured Query Language) (sql)

In this syntax, s1 and s2 are expressions that evaluate character strings.

If s1 and s2 evaluate to numeric, datetime, or boolean values, the CONCAT() function implicitly cast these values to VARCHAR.

The CONCAT() function returns a string that consists of the first argument (s1) followed by the second argument (s2).

The CONCAT() function returns NULL if either s1 or s2 is NULL.

Db2 CONCAT() function examples

Let’s take some examples of using the CONCAT() function.

1) Using CONCAT() function to concatenate strings examples

This example uses the CONCAT() function to concatenate two literal strings:

SELECT 
    CONCAT('IBM','Db2') result
FROM 
    SYSIBM.SYSDUMMY1;
Code language: SQL (Structured Query Language) (sql)

Here is the output:

RESULT 
------ 
IBMDb2 
Code language: SQL (Structured Query Language) (sql)

To add a space between two arguments, you need to call the CONCAT() function twice:

SELECT 
    CONCAT(CONCAT('IBM',' '),'Db2') result
FROM 
    SYSIBM.SYSDUMMY1;
Code language: SQL (Structured Query Language) (sql)

The output is the following:

RESULT  
------- 
IBM Db2 
Code language: SQL (Structured Query Language) (sql)

In this example, the first CONCAT() function concatenates the IBM with space and the second CONCAT() function concatenates the result of the first one with the string Db2.

The following statement uses the concatenation operator ( ||) that returns the same result:

SELECT 
    'IBM' || ' ' || 'DB2'
FROM
    SYSIBM.SYSDUMMY1;
Code language: SQL (Structured Query Language) (sql)

2) Using CONCAT() function with NULL example

See the following authors table from the sample database.

The following example uses the CONCAT() function to concatenate first and middle names of authors:

SELECT
    CONCAT(first_name, 
        CASE 
            WHEN middle_name IS NULL THEN '' 
            ELSE CONCAT(' ',middle_name) 
        END)
FROM
    authors
ORDER BY 
    middle_name, 
    last_name;	
Code language: SQL (Structured Query Language) (sql)

Here is the result set:

Because the middle name can be NULL, we used the CASE expression to return the string '' if the middle name is NULL. If the middle name is not null the CASE expression returns the middle name left-padded with space.

The CONCAT() function concatenated the first name with either string '', or the middle name left-padded with space.

In this tutorial, you have learned how to use the Db2 CONCAT() function to concatenate two string into a single string.