Oracle / PLSQL: CONCAT Function

This Oracle tutorial explains how to use the Oracle/PLSQL CONCAT function with syntax and examples.

Description

The Oracle/PLSQL CONCAT function allows you to concatenate two strings together.

Syntax

The syntax for the CONCAT function in Oracle/PLSQL is:

CONCAT( string1, string2 )

Parameters or Arguments

string1

The first string to concatenate.

string2

The second string to concatenate.

Note

• See also the || operator.

Returns

The CONCAT function returns a string value.

Applies To

The CONCAT function can be used in the following versions of Oracle/PLSQL:

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

Example

Let's look at some Oracle CONCAT function examples and explore how to use the CONCAT function in Oracle/PLSQL.

For example:

CONCAT('Tech on', ' the Net')
Result: 'Tech on the Net'

CONCAT('a', 'b')
Result: 'ab'

Concatenate More Than 2 Values

In Oracle, the CONCAT function will only allow you to concatenate two values together. If you want to concatenate more values than two, you can nest multiple CONCAT function calls.

For example:

SELECT CONCAT(CONCAT('A', 'B'),'C')
FROM dual;
Result: 'ABC'

This example would concatenate the 3 values together and return 'ABC'.

To concatenate 4 values, you can nest one more CONCAT function call.

For example:

SELECT CONCAT(CONCAT(CONCAT('A', 'B'),'C'),'D')
FROM dual;
Result: 'ABCD'

This example would concatenate the 4 values together and return 'ABCD'.

Concatenate Single Quotes

Since the parameters within the CONCAT function are separated by single quotes, it isn't straight forward how to add a single quote character within the result of the CONCAT function.

Let's look at a fairly easy example that shows how to add a single quote to the resulting string using the CONCAT function.

For example:

SELECT CONCAT('Let''s', ' learn Oracle')
FROM dual;
Result: Let's learn Oracle

Since our parameters are enclosed in single quotes, we use 2 additional single quotes within the surrounding quotes to represent a single quotation mark in the resulting concatenated string.

Frequently Asked Questions

Question: How can you use the CONCAT function to concatenate more than 2 strings together?

Answer: Since the CONCAT function will only let you concatenate 2 strings, you will need to nest multiple CONCAT functions to concatenate more than 2 strings together.

For example, to concatenate 3 strings, you could nest the CONCAT function as follows:

CONCAT( CONCAT( string1, string2 ), string3 )

Or you could nest the CONCAT function as follows, if you wanted to concatenate 4 strings:

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

Here is an example provided by Ruth that demonstrates how to nest multiple CONCAT functions to concatenate 6 strings:

CONCAT( CONCAT( CONCAT( CONCAT( CONCAT(
 'I like ', t.type_desc_column), ' cake with '), t.icing_desc_column),' and a '),
   t.fruit_desc_column)

The CONCAT function is one method to concatenate strings in Oracle. An alternative to using the CONCAT function would be to use the || operator to catenatenate multiple strings, as follows:

'I like ' || t.type_desc_column || ' cake with ' || t.icing_desc_column || ' and a '
  || t.fruit_desc_column