DB2 Tutorial

  • Home
  • Start Here
  • Basics
  • Views
  • Triggers
  • Indexes
  • Functions
    • Aggregate Functions
    • Date Functions
    • String Functions
    • Window Functions
Home » Db2 String Functions » Db2 CONCAT

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);

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;

Here is the output:

RESULT ------ IBMDb2

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

SELECT CONCAT(CONCAT('IBM',' '),'Db2') result FROM SYSIBM.SYSDUMMY1;

The output is the following:

RESULT ------- IBM Db2

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;

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;

Here is the result set:

Db2 CONCAT function example

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.

  • Was this tutorial helpful ?
  • YesNo
Next Db2 INITCAP

Getting Started

  • What is Db2
  • Installing Db2 Database Server
  • Db2 Sample Database
  • Creating a Db2 Sample Database
  • Connecting to a Db2 Database
  • Interacting with Db2 using SQL Developer

Data Manipulation

  • SELECT
  • ORDER BY
  • WHERE
  • SELECT DISTINCT
  • AND
  • OR
  • BETWEEN
  • LIKE
  • IN
  • LIMIT
  • FETCH
  • Join
  • INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN
  • FULL OUTER JOIN
  • Self-Join
  • CROSS JOIN
  • GROUP BY
  • Subquery
  • HAVING
  • UNION
  • INTERSECT
  • EXCEPT
  • Common Table Expression or CTE
  • INSERT
  • INSERT Multiple Rows
  • INSERT INTO SELECT
  • UPDATE
  • DELETE

Managing Database Objects

  • CREATE TABLE
  • Identity Columns
  • ALTER COLUMN
  • ADD COLUMN
  • DROP COLUMN
  • DROP TABLE
  • TRUNCATE TABLE
  • RENAME TABLE

Db2 Constraints

  • Primary Key
  • Foreign Key
  • DEFAULT
  • NOT NULL
  • CHECK
  • UNIQUE

Db2 Data Types

  • Integer
  • Decimal
  • VARCHAR
  • CHAR
  • DATE
  • TIME
  • TIMESTAMP

Useful Functions & Expressions

  • CAST
  • CASE Expression
  • COALESCE

About db2tutorial.com

The db2tutorial.com website provides you with a comprehensive IBM DB2 tutorial with many practical examples and hands-on sessions.

Recent Tutorials

  • Db2 Functions
  • Db2 RANK
  • Db2 ROW_NUMBER
  • Db2 Window Functions
  • What is Db2

Site Links

  • Home
  • About Us
  • Contact Us
  • Privacy Policy
Copyright © © 2021 by www.db2tutorial.com. All Rights Reserved.