DB2 Concat

Introduction to DB2 Concat

DB2 concat is a scalar function in DB2 database management system which helps us to join two or more expressions into a single expression. This helps us combine the result. In this article, we will learn about concat function, its syntax, types of the parameters supported by the function and the type of the result that is returned after concatenating the two values.

Syntax of DB2 Concat

The syntax of the DB2 concat scalar function is as shown below:

CONCAT (expression1, expression2)

In the above syntax, expression1 and expression2 can be any parameter that evaluates to anyone of the following datatypes-

• Graphic string
• Binary value
• Character value
• Numeric value
• Boolean value
• Datetime value

If the expression evaluates to a Boolean, datetime or numeric value then they are implicitly internally converted into the varchar datatype by the DB2 DBMS. We can either specify a field names of the database, variable name, string expressions or an implicit fixed value as the parameters of the concat function to join the two values and get the one. There are three different possibilities of using the CONCAT function based on whether the parameters are the database field values or the string expressions which are as follows:

• CONCAT({database_field_name1}, {database_field_name2})
• CONCAT({string_expression1}, { string_expression2})
• CONCAT({database_field_name1}, { string_expression2})

When using the concat scalar function, we have to be careful as there are certain rules to be followed while using it and has following restrictions on usage:

• If one of the arguments of the concat function is specified in CODEUNITS32 format then we cannot mention the other remaining argument in FOR BIT DATA format.
• If there is a requirement of concatenating the binary string, then it can be concatenated with only another binary string or even with another character provided that character is specified in FOR BIT DATA format.
• If we have Unicode database, only at that time we can concatenate the graphic string and character strings. If we have specified any parameter as character string, then that string is firstly converted into the graphic string before being concatenated by the concat function.
• We cannot define the character string in the format of FOR BIT DATA. The reason for this is that character strings can never be converted into the graphic data strings through casting.

Resultant – The CONCAT scalar function always returns a string in DB2 DBMS. The resultant string is a joined string that is collectively formed by joining first and the second argument in the same order. The length and the data type of the output resultant is actually dependent on the length and data type of the arguments that are passed as the parameter to the CONCAT function. If either of the expression is set to the NULL value then CONCAT function returns the NULL value as the resultant. In case, if we want to concat more than two values then we have to use concat function multiple times. An alternative way of doing this is by using the || double pipe symbol. However, sometimes DB2 doesn’t support the usage of double pipe symbols and gives errors in the output when used. Hence, usage of multiple Concat statements is always a helpful and safer way to concat more than two values.

Examples of DB2 Concat

Let us consider two columns of the Customers table namely f_name and l_name. The table contains the following data in it.

If we want to join the column value of first_name and the last name of the customers table then we can do that by using the concat function and the following query statement.

SELECT CONCAT(first_name, last_name) as name FROM Customers;

The output of the above query gives the following output –

As we can see that the name values that are returned are concatenated to each other with first_name value before the last_name column value. If we want a spacing of one space in between the first name and the last name column values of the table then we can use concat function to concat the firstname and the space firstly and then one more concat function which will concat the resultant of the first concat function and the last name to form the name which will contain the concatenated value having first name space and then the last name at the end. Our query statement can be used in the following way –

SELECT CONCAT(CONCAT(first_name," "),last_name) as name FROM Customers;

The output of the above query statement is as shown in the below image having one space in between the first name and the last name –

Alternatively, we can make the use of the following query statement that helps us to concat one or more expression values to form a name having space in between first and last name –

SELECT first_name || " " || last_name FROM Customers;

The output of above query is similar to previous one as shown below –

If we insert any NULL value in any of the two columns of customers table then the resultant concatenated string will also be NULL value. For example, let us consider that the data of the customers table is modified to the following content –

If we make the use of any of the second or third query to concatenate the values of first name and last name with spacing in between or even the first query without space in between then the output will contain NULL value for the two results having NULL value in first and the last name respectively. The following is the output that we get when NULL values are concatenated –

If we want to ignore the NULL values and replace them with blank space, then we can make the use of case statement to concatenate the values as shown below –

SELECT
CONCAT(
CASE
WHEN first_name IS NULL THEN ''
ELSE CONCAT(' ',first_name)
END,
CASE
WHEN last_name  IS NULL THEN ''
ELSE CONCAT(' ',last_name)
END
)
FROM
Customers;

The output of above query is as shown in the below image –

Recommended Articles

We hope that this EDUCBA information on “DB2 Concat” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

1. What is DB2?
2. Data Warehouse Software
3. Flask DB Migrate
4. DB2 Interview Questions