{"id":18940,"date":"2021-07-30T08:19:06","date_gmt":"2021-07-29T22:19:06","guid":{"rendered":"https:\/\/database.guide\/?p=18940"},"modified":"2021-07-30T08:19:06","modified_gmt":"2021-07-29T22:19:06","slug":"concat-function-in-oracle","status":"publish","type":"post","link":"https:\/\/database.guide\/concat-function-in-oracle\/","title":{"rendered":"CONCAT() Function in Oracle"},"content":{"rendered":"\n

In Oracle, the CONCAT()<\/code> function performs a string concatenation on its arguments. It returns the string that results from concatenating its arguments.<\/p>\n\n\n\n

Concatenation is the operation of joining multiple strings end-to-end.<\/p>\n\n\n\n\n\n\n\n

Syntax<\/h2>\n\n\n\n

The syntax goes like this:<\/p>\n\n\n\n

CONCAT(char1, char2)<\/code><\/pre>\n\n\n\n
Where both arguments can be any of the data types CHAR<\/code>, VARCHAR2<\/code>, NCHAR<\/code>, NVARCHAR2<\/code>, CLOB<\/code>, or NCLOB<\/code>. <\/p>\n\n\n\n
Example<\/h2>\n\n\n\n
Here’s a simple example to demonstrate:<\/p>\n\n\n\n
SELECT CONCAT('Ponzi', 'Invest')\nFROM DUAL;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
   CONCAT('PONZI','INVEST') \n___________________________ \nPonziInvest                <\/pre>\n\n\n\n
Null Values<\/h2>\n\n\n\n
Concatenating a string with null<\/code> returns the string:<\/p>\n\n\n\n
SET NULL 'null';\n\nSELECT \n    CONCAT('Ponzi', null),\n    CONCAT(null, 'Invest')\nFROM DUAL;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
   CONCAT('PONZI',NULL)    CONCAT(NULL,'INVEST') \n_______________________ ________________________ \nPonzi                   Invest                   <\/pre>\n\n\n\n
But if both arguments are null<\/code>, the result is null<\/code>:<\/p>\n\n\n\n
SET NULL 'null';\n\nSELECT CONCAT(null, null)\nFROM DUAL;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
   CONCAT(NULL,NULL) \n____________________ \nnull                <\/pre>\n\n\n\n
By default, SQLcl and SQL*Plus return a blank space whenever null<\/code> occurs as a result of a SQL SELECT<\/a><\/code> statement. <\/p>\n\n\n\n
However, you can use SET NULL<\/a><\/code> to specify a different string to be returned. Here I specified that the string null<\/code> should be returned.<\/p>\n\n\n\n
Incorrect Argument Count<\/h2>\n\n\n\n
Calling CONCAT()<\/code> without passing any arguments returns an error:<\/p>\n\n\n\n
SELECT CONCAT()\nFROM DUAL;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
Error starting at line : 1 in command -\nSELECT CONCAT()\nFROM DUAL\nError at Command Line : 1 Column : 8\nError report -\nSQL Error: ORA-00938: not enough arguments for function\n00938. 00000 -  \"not enough arguments for function\"\n*Cause:    \n*Action:<\/pre>\n\n\n\n
And passing the wrong number of arguments results in an error:<\/p>\n\n\n\n
SELECT CONCAT('Gosh', 'Dang', 'Investments')\nFROM DUAL;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
Error starting at line : 1 in command -\nSELECT CONCAT('Gosh', 'Dang', 'Investments')\nFROM DUAL\nError at Command Line : 1 Column : 8\nError report -\nSQL Error: ORA-00909: invalid number of arguments\n00909. 00000 -  \"invalid number of arguments\"\n*Cause:    \n*Action:<\/pre>\n\n\n\n
Character Set & Data Type<\/h2>\n\n\n\n
The resulting string is in the same character set as the first argument, however its data type depends on the data types of the arguments.<\/p>\n\n\n\n
When concatenating two different data types, Oracle Database returns the data type that results in a lossless conversion. Therefore, if one of the arguments is a LOB, then the returned value is a LOB. If one of the arguments is a national data type, then the returned value is a national data type. <\/p>\n\n\n\n
Examples:<\/p>\n\n\n\n
  • CONCAT<\/code>(CLOB<\/code>, NCLOB<\/code>) returns NCLOB<\/code><\/li>
  • CONCAT<\/code>(NCLOB<\/code>, NCHAR<\/code>) returns NCLOB<\/code><\/li>
  • CONCAT<\/code>(NCLOB<\/code>, CHAR<\/code>) returns NCLOB<\/code><\/li>
  • CONCAT<\/code>(NCHAR<\/code>, CLOB<\/code>) returns NCLOB<\/code><\/li><\/ul>\n","protected":false},"excerpt":{"rendered":"
    In Oracle, the CONCAT() function performs a string concatenation on its arguments. It returns the string that results from concatenating its arguments. Concatenation is the operation of joining multiple strings end-to-end.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[41],"tags":[93,67,20],"class_list":["post-18940","post","type-post","status-publish","format-standard","hentry","category-oracle","tag-functions","tag-string-functions","tag-what-is"],"_links":{"self":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/18940","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/comments?post=18940"}],"version-history":[{"count":4,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/18940\/revisions"}],"predecessor-version":[{"id":19182,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/18940\/revisions\/19182"}],"wp:attachment":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/media?parent=18940"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/categories?post=18940"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/tags?post=18940"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}