CONCAT() Function in Oracle

by

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) returns NCLOB
  • CONCAT(NCLOB, NCHAR) returns NCLOB
  • CONCAT(NCLOB, CHAR) returns NCLOB
  • CONCAT(NCHAR, CLOB) returns NCLOB