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:

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 return type of CONCAT depends on the types of Expression1 and Expression2. The following table summarizes 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)

  
  

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

• The concatenation of CHAR, NCHAR, VARCHAR2, and NVARCHAR2 types are supported. The result type of character types concatenated with ncharacter types is ncharacter types.

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.

The description of the | | operator is in the section "Expression specification".