EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login
Home Data Science Data Science Tutorials Database Management Tutorial DB2 Concat
Secondary Sidebar
OLTP vs OLAP

Clustering Methods

Clustering Algorithms

Pie Chart in R

Data Science vs Data Engineering

What is MapReduce in Hadoop?

DB2 Concat

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:

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

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.

DB2 Concat-1

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 –

DB2 Concat-1.1

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 –

DB2 Concat-1.2

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 –

DB2 Concat-1.3

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 –

DB2 Concat-1.4

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 –

Output-1.5

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 –

Output-1.6

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
Popular Course in this category
SQL Training Program (10 Courses, 8+ Projects)
  10 Online Courses |  8 Hands-on Projects |  80+ Hours |  Verifiable Certificate of Completion
4.5
Price

View Course

Related Courses

PL SQL Training (4 Courses, 2+ Projects)4.9
Oracle Training (17 Courses, 8+ Projects)4.8
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more