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.
Syntax
The syntax goes like this:
CONCAT(char1, char2)
Where both arguments can be any of the data types CHAR
, VARCHAR2
, NCHAR
, NVARCHAR2
, CLOB
, or NCLOB
.
Example
Here’s a simple example to demonstrate:
SELECT CONCAT('Ponzi', 'Invest')
FROM DUAL;
Result:
CONCAT('PONZI','INVEST') ___________________________ PonziInvest
Null Values
Concatenating a string with null
returns the string:
SET NULL 'null';
SELECT
CONCAT('Ponzi', null),
CONCAT(null, 'Invest')
FROM DUAL;
Result:
CONCAT('PONZI',NULL) CONCAT(NULL,'INVEST') _______________________ ________________________ Ponzi Invest
But if both arguments are null
, the result is null
:
SET NULL 'null';
SELECT CONCAT(null, null)
FROM DUAL;
Result:
CONCAT(NULL,NULL) ____________________ null
By default, SQLcl and SQL*Plus return a blank space whenever null
occurs as a result of a SQL SELECT
statement.
However, you can use SET NULL
to specify a different string to be returned. Here I specified that the string null
should be returned.
Incorrect Argument Count
Calling CONCAT()
without passing any arguments returns an error:
SELECT CONCAT()
FROM DUAL;
Result:
Error starting at line : 1 in command - SELECT CONCAT() FROM DUAL Error at Command Line : 1 Column : 8 Error report - SQL Error: ORA-00938: not enough arguments for function 00938. 00000 - "not enough arguments for function" *Cause: *Action:
And passing the wrong number of arguments results in an error:
SELECT CONCAT('Gosh', 'Dang', 'Investments')
FROM DUAL;
Result:
Error starting at line : 1 in command - SELECT CONCAT('Gosh', 'Dang', 'Investments') FROM DUAL Error at Command Line : 1 Column : 8 Error report - SQL Error: ORA-00909: invalid number of arguments 00909. 00000 - "invalid number of arguments" *Cause: *Action:
Character Set & Data Type
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.
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.
Examples:
CONCAT
(CLOB
,NCLOB
) returnsNCLOB
CONCAT
(NCLOB
,NCHAR
) returnsNCLOB
CONCAT
(NCLOB
,CHAR
) returnsNCLOB
CONCAT
(NCHAR
,CLOB
) returnsNCLOB