Oracle CONCAT() Function

Oracle CONCAT() is a built-in function that returns the concatenated string of two given parameters.

Oracle CONCAT() syntax

Here is the syntax for the Oracle CONCAT() function:

CONCAT(char1, char2)

CONCAT(char1, char2) is equivalent to the expression char1 || char2 with the concatenation operator (||).

Parameters

char1

Required. The value that needs to be concatenated. It can be any of the data types CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.

char2

Required. The value that needs to be concatenated. It can be any of the data types CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.

Return Value

The Oracle CONCAT() function returns a string that is the concatenation result of the two parameter values.

For two different data types, Oracle CONCAT() returns the data type that results in a lossless conversion. Therefore, if one of the parameters is a LOB, the return value is a LOB. If one of the parameters is a national data type, the return value is a national data type. For example:

  • CONCAT(CLOB, NCLOB) returns NCLOB
  • CONCAT(NCLOB, NCHAR) returns NCLOB
  • CONCAT(NCLOB, CHAR) returns NCLOB
  • CONCAT(NCHAR, CLOB) returns NCLOB

Oracle CONCAT() Examples

Here are some examples that demonstrate the usage of the Oracle CONCAT() function.

Basic Usage

SELECT
    CONCAT('Hello', 'World')
FROM dual;

Output:

CONCAT('HELLO','WORLD')
__________________________
HelloWorld

NULL Parameters

If one of the arguments is NULL, the function will return the other parameter which is not NULL.

SELECT
    CONCAT(NULL, 'A'),
    CONCAT('B', NULL)
FROM dual;

Output:

CONCAT(NULL,'A')    CONCAT('B',NULL)
___________________ ___________________
A                   B

This function will return NULL if all of the parameters are NULL.

SET NULL 'NULL';
SELECT
    CONCAT(NULL, NULL)
FROM dual;

Output:

CONCAT(NULL,NULL)
____________________
NULL

In this example, we use the statement SET NULL 'NULL'; to display NULL values as the string 'NULL'.

Conclusion

Oracle CONCAT() is a built-in function that returns the concatenated string of two given parameters.