Oracle® Database SQL Reference 10g Release 1 (10.1) Part Number B10759-01 |
|
|
View PDF |
CONCAT
returns char1
concatenated with char2
. Both char1
and char2
can be any of the datatypes CHAR
, VARCHAR2
, NCHAR
, NVARCHAR2
, CLOB
, or NCLOB
. The string returned is in the same character set as char1
. Its datatype depends on the datatypes of the arguments.
In concatenations of two different datatypes, Oracle Database returns the datatype 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 datatype, then the returned value is a national datatype. For example:
CONCAT
(CLOB
, NCLOB
) returns NCLOB
CONCAT
(NCLOB
, NCHAR
) returns NCLOB
CONCAT
(NCLOB
, CHAR
) returns NCLOB
CONCAT
(NCHAR
, CLOB
) returns NCLOB
This function is equivalent to the concatenation operator (||). The function is useful when there are spaces in the values to be concatenated. The concatenation operator does not permit spaces.
This example uses nesting to concatenate three character strings:
SELECT CONCAT(CONCAT(last_name, '''s job category is '), job_id) "Job" FROM employees WHERE employee_id = 152; Job ------------------------------------------------------ Hall's job category is SA_REP