{"id":817,"date":"2019-06-09T22:53:07","date_gmt":"2019-06-10T06:53:07","guid":{"rendered":"https:\/\/db2tutorial.com\/?page_id=817"},"modified":"2020-04-11T06:58:00","modified_gmt":"2020-04-11T14:58:00","slug":"db2-concat","status":"publish","type":"page","link":"https:\/\/www.db2tutorial.com\/db2-string-functions\/db2-concat\/","title":{"rendered":"Db2 CONCAT"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the Db2 CONCAT()<\/code> function to concatenate two strings into a single string.<\/p>\n\n\n\n

Db2 CONCAT() function overview<\/h2>\n\n\n\n

The CONCAT()<\/code> function accepts two string arguments and concatenates these strings into a single string.<\/p>\n\n\n\n

Here is the syntax of the CONCAT()<\/code> function:<\/p>\n\n\n

CONCAT(s1, s2);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax, s1 and s2 are expressions that evaluate character strings.<\/p>\n\n\n\n
If s1 and s2 evaluate to numeric, datetime, or boolean values, the CONCAT()<\/code> function implicitly cast<\/a> these values to VARCHAR<\/a><\/code>.<\/p>\n\n\n\n
The CONCAT()<\/code> function returns a string that consists of the first argument (s1) followed by the second argument (s2).<\/p>\n\n\n\n
The CONCAT()<\/code> function returns NULL if either s1 or s2 is NULL.<\/p>\n\n\n\n
Db2 CONCAT() function examples<\/h2>\n\n\n\n
Let’s take some examples of using the CONCAT()<\/code> function.<\/p>\n\n\n\n
1) Using CONCAT() function to concatenate strings examples<\/h3>\n\n\n\n
This example uses the CONCAT()<\/code> function to concatenate two literal strings:<\/p>\n\n\n
SELECT<\/span> \n    CONCAT<\/span>('IBM'<\/span>,'Db2'<\/span>) result<\/span>\nFROM<\/span> \n    SYSIBM.SYSDUMMY1;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Here is the output:<\/p>\n\n\n
RESULT \n------ <\/span>\nIBMDb2 \n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
To add a space between two arguments, you need to call the CONCAT()<\/code> function twice:<\/p>\n\n\n
SELECT<\/span> \n    CONCAT<\/span>(CONCAT<\/span>('IBM'<\/span>,' '<\/span>),'Db2'<\/span>) result<\/span>\nFROM<\/span> \n    SYSIBM.SYSDUMMY1;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The output is the following:<\/p>\n\n\n
RESULT  \n------- <\/span>\nIBM Db2 \n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this example, the first CONCAT()<\/code> function concatenates the IBM<\/code> with space and the second CONCAT()<\/code> function concatenates the result of the first one with the string Db2<\/code>.<\/p>\n\n\n\n
The following statement uses the concatenation operator ( ||<\/code>) that returns the same result:<\/p>\n\n\n
SELECT<\/span> \n    'IBM'<\/span> || ' '<\/span> || 'DB2'<\/span>\nFROM<\/span>\n    SYSIBM.SYSDUMMY1;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
2) Using CONCAT() function with NULL example<\/h3>\n\n\n\n
See the following authors<\/code> table from the sample database<\/a>.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
The following example uses the CONCAT()<\/code> function to concatenate first and middle names of authors:<\/p>\n\n\n
SELECT<\/span>\n    CONCAT<\/span>(first_name, \n        CASE<\/span> \n            WHEN<\/span> middle_name IS<\/span> NULL<\/span> THEN<\/span> ''<\/span> \n            ELSE<\/span> CONCAT<\/span>(' '<\/span>,middle_name) \n        END<\/span>)\nFROM<\/span>\n    authors<\/span>\nORDER<\/span> BY<\/span> \n    middle_name, \n    last_name;\t\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Here is the result set:<\/p>\n\n\n\n
\"Db2<\/figure>\n\n\n\n
Because the middle name can be NULL, we used the CASE<\/code> expression to return the string ''<\/code> if the middle name is NULL. If the middle name is not null the CASE expression returns the middle name left-padded with space.<\/p>\n\n\n\n
The CONCAT()<\/code> function concatenated the first name with either string ''<\/code>, or the middle name left-padded with space.<\/p>\n\n\n\n
In this tutorial, you have learned how to use the Db2 CONCAT()<\/code> function to concatenate two string into a single string.<\/p>\n
\n\t
\n\t\t
Was this tutorial helpful ?<\/div>\n\t\t
\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
\n\t\t
\n\t\t\t
<\/div>\n\t\t\t\n\t\t\t