This Oracle tutorial explains how to use the Oracle/PLSQL CONCAT function with syntax and examples.
The Oracle/PLSQL CONCAT function allows you to concatenate two strings together.
The syntax for the CONCAT function in Oracle/PLSQL is:
The first string to concatenate.
The second string to concatenate.
The CONCAT function returns a string value.
The CONCAT function can be used in the following versions of Oracle/PLSQL:
Let's look at some Oracle CONCAT function examples and explore how to use the CONCAT function in Oracle/PLSQL.
For example:
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:
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:
This example would concatenate the 4 values together and return 'ABCD'.
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:
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.
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:
Or you could nest the CONCAT function as follows, if you wanted to concatenate 4 strings:
Here is an example provided by Ruth that demonstrates how to nest multiple CONCAT functions to concatenate 6 strings:
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: