SQL CONCAT() Function

SQL CONCAT() function concatenated string1 with string2 and return to a concatenated string.

SQL CONCAT() function support any of the character data types CHAR, NCHAR, VARCHAR2, NVARCHAR2, CLOB, NCLOB.

You can concat two different data types. if any one of the argument data type is national type (NCHAR, NVARCHAR2). then returning value is a national data type.

Supported Oracle SQL version

  • Oracle 8i
  • Oracle 9i
  • Oracle 10g
  • Oracle 11g
  • Oracle 12c
  • Oracle 18c

Syntax

CONCAT(string1, string2) 

CONCAT function you can use nested by following way,

CONCAT(CONCAT(CONCAT(string1, string2),string3), string4) 

Example

Consider following example return the concatenated value of given argument.

SQL> SELECT CONCAT('HELLO ', 'Opal Kole') FROM DUAL;

CONCAT('HELLO',
---------------
HELLO Opal Kole

SQL> SELECT CONCAT(CONCAT( CONCAT('HELLO ', 'Opal Kole'), ' Whats'), ' Up?') "NESTED CONCAT" FROM DUAL;

NESTED CONCAT
-------------------------
HELLO Opal Kole Whats Up?

Alternative you can use || (double pipe sign) for concatenate two strings.

SQL> SELECT 'HELLO ' || 'Opal Kole' FROM DUAL;

'HELLO'||'OPALK
---------------
HELLO Opal Kole