CONCAT

The CONCAT function concatenates one character string with another to form a new character string.

SQL syntax

CONCAT(Expression1, Expression2)

Parameters

CONCAT has the parameters:

Parameter Description

Expression1

A CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB expression.

Expression2

A CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB expression.

Description

  • CONCAT returns Expression1 concatenated with Expression2.

  • The type of Expression1 and Expression2 must be compatible.

  • If Expression2 is NULL, CONCAT returns Expression1. If Expression1 is NULL, CONCAT returns Expression2.

  • If both Expression1 and Expression2 are NULL, CONCAT returns NULL.

  • The treatment of NCHAR and NVARCHAR2 is similar. If one of the operands is of varying length, the result is of varying length. Otherwise the result is of a fixed length.

  • The return data type of CONCAT depends on the types of Expression1 and Expression2. In concatenations of two different data types, the database returns the data type that can contain the result. Therefore, if one of the arguments is a national character data type, the returned value is a national character data type. If one of the arguments is a LOB, the returned value is a LOB.

    The following table provides examples of how the return type is determined.

    Expression1 Expression2 CONCAT

    CHAR(m)

    CHAR(n)

    CHAR(m+n)

    CHAR(m)

    VARCHAR2(n)

    VARCHAR2(m+n)

    VARCHAR2(m)

    CHAR(n)

    VARCHAR2(m+n)

    VARCHAR2(m)

    VARCHAR2(n)

    VARCHAR2(m+n)

    CLOB

    NCLOB

    NCLOB

    NCLOB

    NCHAR

    NCLOB

    NCLOB

    CHAR(n)

    NCLOB

    NCHAR(n)

    CLOB

    NCLOB

Examples

The following example concatenates first names and last names.

Command> SELECT CONCAT(CONCAT(first_name, ' '), last_name), salary 
         FROM employees;
< Steven King, 24000 >
< Neena Kochhar, 17000 >
< Lex De Haan, 17000 >
< Alexander Hunold, 9000 >
...
107 rows found.

The following example concatenates column id with column id2. In this example, the result type is NCHAR(40). 

Command> CREATE TABLE cat (id CHAR (20), id2 NCHAR (20));
Command> INSERT INTO cat VALUES ('abc', 'def');
1 row inserted.
Command> SELECT CONCAT (id,id2) FROM cat;
< abc                 def                  >
1 row found.

See "Expression Specification" for a description of the || operator.