FB

Oracle CONCAT FunctionThe 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:

CONCAT( 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.

Get Your Free PDF: 9 Ways to Improve your Database Skills

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 which 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.

SELECT CONCAT ('Complete ', 'IT Professional') AS NEW_CONCAT
FROM DUAL;

Result:

NEW_CONCAT
Complete IT Professional

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

 

Example 2

This example uses two column values.

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

Result:

NEW_CONCAT
JohnSmith
SallyJones
SteveBrown
AdamCooper
JoshThompson
PeterManson
MarkAllan

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.

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

Result:

NEW_CONCAT
My Résumé

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.

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

Result:

NEW_CONCAT
10 Long RoadUSA
50 Market StreetUSA
201 Flinders LaneCanada
14 Wellington RoadUSA
80 Victoria Street
5 Johnson StFrance
8 Smith StreetUK

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

 

Example 5

We can also use double pipes to concatenate values.

SELECT first_name || last_name AS NEW_CONCAT
FROM customers;

Result:

NEW_CONCAT
JohnSmith
SallyJones
SteveBrown
AdamCooper
JoshThompson
PeterManson
MarkAllan

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.

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

Result:

NEW_CONCAT
JohnSmithUSA
SallyJonesUSA
SteveBrownCanada
AdamCooperUSA
JoshThompson
PeterMansonFrance
MarkAllanUK

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.

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

Result:

NEW_CONCAT
10 Long Road, USA
50 Market Street, USA
201 Flinders Lane, Canada
14 Wellington Road, USA
80 Victoria Street,
5 Johnson St, France
8 Smith Street, UK

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.

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

Result:

NEW_CONCAT
John Smith – 10 Long Road
Sally Jones – 50 Market Street
Steve Brown – 201 Flinders Lane
Adam Cooper – 14 Wellington Road
Josh Thompson – 80 Victoria Street
Peter Manson – 5 Johnson St
Mark Allan – 8 Smith Street

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.

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

Result:

NEW_CONCAT
John Smith – 10 Long Road
Sally Jones – 50 Market Street
Steve Brown – 201 Flinders Lane
Adam Cooper – 14 Wellington Road
Josh Thompson – 80 Victoria Street
Peter Manson – 5 Johnson St
Mark Allan – 8 Smith Street

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.

Lastly, if you enjoy the information and career advice I’ve been providing, sign up to my newsletter below to stay up-to-date on my articles. You’ll also receive a fantastic bonus. Thanks!

Get Your Free PDF: 9 Ways to Improve your Database Skills

Image courtesy of digitalart / FreeDigitalPhotos.net

Get Your FREE PDF: 9 Ways to Improve your Database Skills