CONCAT
Syntax
Purpose
CONCAT
takes as input two or more arguments and returns the concatenation of all arguments.
The arguments can be any of the data types CHAR
, VARCHAR2
, NCHAR
, NVARCHAR2
, CLOB
, or NCLOB
. Arguments of other data types are implicitly converted to VARCHAR2
before concatenation.
The string returned is in the same character set as char1
. Its data type depends on the data types of the arguments.
In concatenations of two or more 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.
Rules for the Data Types of Return Values
Among all arguments:
-
if there is a NCLOB, or if there is a CLOB and a NVARCHAR2 /NCHAR, then the return type is NCLOB.
-
otherwise, if there is CLOB, then the return type is CLOB
-
otherwise, if there is NVARCHAR2, or if there is a VARCHAR2 and a NCHAR, then the return type is NVARCHAR2
-
otherwise, if there is VARCHAR2, then the return type is VARCHAR2
-
otherwise, if there is NCHAR, then the return type is NCHAR
-
otherwise, the return type is CHAR
Examples of Data Types Returned
CONCAT
(CLOB
, NCLOB
) returns NCLOB
CONCAT
(CLOB
, NCHAR
) returns NCLOB
CONCAT
(CLOB
, CHAR
) returns CLOB
CONCAT
(VARCHAR2
, NCHAR
) returns NVARCHAR2
CONCAT
(CHAR
, VARCHAR2
) returns VARCHAR2
CONCAT
(CHAR
, VARCHAR2
, CLOB
) returns CLOB
CONCAT
(CHAR
, NVARCHAR2
, CLOB
) returns NCLOB
This function is equivalent to the concatenation operator (||).
See Also:
-
Concatenation Operator for information on the
CONCAT
operator -
Appendix C in Oracle Database Globalization Support Guide for the collation derivation rules, which define the collation assigned to the character return value of
CONCAT
Examples
This example concatenates three character strings:
SELECT CONCAT( last_name, '''s job category is ', job_id) "Job" FROM employees WHERE employee_id = 152; Job ------------------------------------------------------ Hall's job category is SA_REP