7 Post
7 Post
10 Post
2 Post
1 Post
5 News
Oracle CONCAT Function
The CONCAT function joins two expressions together into a single string.
Syntax
CONCAT(string1, string2)
Description
-
The function concatenates two strings.
-
If more than two strings must be concatenated, you must either:
-
nest
CONCATfunctions, or -
use the concatenation operator
||.
-
Example with nesting:
SELECT CONCAT(CONCAT('Oracle ', 'Text '), 'Search')
FROM dual;
Example using the operator:
SELECT 'Oracle ' || 'Text ' || 'Search'
FROM dual;
FROM dual;
Supported Data Types
The arguments may be any of the following types:
-
CHAR -
VARCHAR2 -
NCHAR -
NVARCHAR2 -
CLOB -
NCLOB
Arguments of other data types are implicitly converted to VARCHAR2 before concatenation.
Return Type Rules
The return type depends on the argument data types.
| Condition | Return Type |
|---|---|
If any argument is NCLOB |
NCLOB |
If there is a CLOB together with NCHAR or NVARCHAR2 |
NCLOB |
If there is a CLOB |
CLOB |
If there is NVARCHAR2 or VARCHAR2 with NCHAR |
NVARCHAR2 |
If there is VARCHAR2 |
VARCHAR2 |
If there is NCHAR |
NCHAR |
| Otherwise | CHAR |
Behavior with NULL Values
CONCAT ignores NULL values.
If one argument is NULL, the result is the other argument.
Example:
SELECT CONCAT('Concat function in Oracle ignores nulls', NULL)
FROM dual;
FROM dual;
Result:
Concat function in Oracle ignores nulls
Important Limitation
Unlike the || operator, the CONCAT function can combine only two strings.
To concatenate multiple values you must nest the function:
SELECT CONCAT(CONCAT('A','B'),'C')
FROM dual;
FROM dual;
or use the preferred operator:
SELECT 'A' || 'B' || 'C'
FROM dual;