Oracle CONCAT Function with Examples

The Oracle CONCAT function is not a very widely used function, but still helpful. Learn more about the CONCAT function in this article.

Purpose of the Oracle CONCAT Function

The Oracle CONCAT function allows you to join, or concatenate, two strings together. It's part of standard string manipulation in many programming languages.

For example:

string1 = "Yes "

string2 = "Please"

To concatenate these strings means to join them together.

string3 = "Yes Please"

Syntax

The syntax of the Oracle CONCAT function is:

1CONCAT( string1, string2 )

The string that is returned is the same data type as string1, which can be any of CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB or NCLOB. However, if you're converting two different data types, the type returned is one that results in a "lossless" conversion. In other words, it keeps the LOB or the Unicode data type if one exists.

The function only allows you to concatenate two strings together. If you want to concatenate multiple strings, you can use nested functions, or the double pipe character, as mentioned below.

Parameters

The parameters of the Oracle CONCAT function are:

• string1 (mandatory): The first string to concatenate as part of this function.
• string2 (mandatory): The second string to concatenate as part of this function.

The parameters are quite simple compared to other functions that we've looked at so far. Both parameters are mandatory and are both string data types.

Let's have a look at some questions about using the CONCAT function and then some examples.

What's the Difference between Oracle CONCAT vs Pipe?

In short, there is no difference in how the concatenation is done.

The purpose of the CONCAT function is to allow scripts to perform concatenation when moving between systems that have different character sets (and therefore may not support the vertical bars or pipes). As stated by Oracle, this was provided as an alternative to the vertical bars.

In most cases, you can use the double pipe || as concatenation, for several reasons:

• It's the ANSI standard
• It's easier to read
• It allows for multiple strings to be concatenated (more than the two permitted by CONCAT)
• It has similar performance

A StackOverflow experiment was run that compared CONCAT and || over a loop of 100 million and found almost identical performance.

Can You Use Oracle CONCAT with Column Values?

Yes, you can. Refer to the Examples section below to see how you can use it with column values. If you want to add a space or other character in between, you'll need to add another nested CONCAT function.

Can You Use Oracle CONCAT on Rows and Group By Rows?

If you want to concatenate values from multiple rows in Oracle, then you don't need to use the CONCAT function. In Oracle 11g onwards, you can use the LISTAGG function.

I haven't included an example here, but when the page for the Oracle LISTAGG function gets published, an example will be included there.

Can Oracle CONCAT CLOB Fields?

Yes, you can use Oracle CONCAT on CLOB fields. It can be used on CLOB or NCLOB data types.

Some example scripts use different functions and loops, but with recent versions of Oracle, you can use this function on CLOBs.

How Can You Use Oracle CONCAT with Space?

To concatenate two fields together and include a space between them essentially means you're concatenating three fields:

• Field 1
• Space
• Field 2

To do this, you'll need to use nested CONCAT functions, to concatenate one field with a space, and then use that result to concatenate with the second field. It can be done in either order.

Refer to the Examples section below for more information.

Examples of the Oracle CONCAT Function

Here are some examples of the CONCAT function. I find that examples are the best way for me to learn about code, even with the explanation above.

Example 1

This is a basic example of the CONCAT function.

1SELECT CONCAT ('Databases ', 'are great') AS NEW_CONCAT
2FROM DUAL;

Result:

As you can see, the two string values have been joined together.

Example 2

This example uses two column values.

1SELECT CONCAT (first_name, last_name) AS NEW_CONCAT
2FROM customers;

Result:

This shows the first name and last name concatenated, but without a space in between.

Example 3

This example shows two strings concatenated, but one has a Unicode value.

1SELECT CONCAT('My ', 'Résumé') AS NEW_CONCAT
2FROM DUAL;

Result:

The result actually includes the Unicode characters and doesn't change them to fit the data type of the first parameter.

Example 4

This example uses a CLOB value (full_address) in the concatenation.

1SELECT CONCAT(full_address, country) AS NEW_CONCAT
2FROM customers;

Result:

The address and country are concatenated just like any other string value.

Example 5

We can also use double pipes to concatenate values.

1SELECT first_name || last_name AS NEW_CONCAT
2FROM customers;

Result:

This shows both first_name and last_name concatenated together, in the same way as Example 2.

Example 6

This example uses a nested CONCAT function to join three strings together.

1SELECT
2CONCAT(CONCAT(first_name, last_name), country) AS NEW_CONCAT
3FROM customers;

Result:

This result joins the first_name, last_name, and country values together, but without spaces.

Example 7

This example joins several strings using nested CONCAT functions, and also includes a space.

1SELECT
2CONCAT(CONCAT(full_address, ', '), country) AS NEW_CONCAT
3FROM customers;

Result:

As you can see, the full_address and country values are separated by a comma and a space.

Example 8

This example shows many nested CONCAT functions.

1SELECT
2CONCAT(CONCAT(CONCAT(CONCAT(first_name, ' '), last_name), ' - '), full_address) AS NEW_CONCAT
3FROM customers;

Result:

The name is joined together, and then a hyphen, then the full address.

Example 9

The same query from Example 8 is done but using pipes instead of the CONCAT function.

1SELECT
2first_name || ' ' || last_name || ' - ' || full_address AS NEW_CONCAT
3FROM customers;

Result:

As you can see, the same result is shown. The first name, last name, and full address are joined together in a much more readable way.

Similar Functions

Some functions which are similar to the CONCAT function are:

• LISTAGG - allows you to order data within a group and concatenate data

You can find a full list of Oracle functions here.