Oracle / PLSQL: || Operator

This Oracle tutorial explains how to use the Oracle/PLSQL || concatenate operator with syntax and examples.

Description

The Oracle/PLSQL || operator allows you to concatenate 2 or more strings together.

Syntax

The syntax for the || operator in Oracle/PLSQL is:

string1 || string2 [ || string_n ]

Parameters or Arguments

string1

The first string to concatenate.

string2

The second string to concatenate.

string_n

Optional. The nth string to concatenate.

Returns

The || operator returns a string value.

Applies To

The || operator 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 concatentaion examples and explore how to use the || operator in Oracle/PLSQL.

For example:

'Tech on' || ' the Net'
Result: 'Tech on the Net'

'a' || 'b' || 'c' || 'd'
Result: 'abcd'

Concatenate Space Characters

When you are concatenating values together, you might want to add space characters to separate your concatenated values. Otherwise, you might get a long string with the concatenated values running together. This makes it very difficult to read the results.

Let's look at an easy example.

We can concatenate a space character using the || operator.

For example:

SELECT 'Dave' || ' ' || 'Anderson'
FROM dual;
Result: 'Dave Anderson'

In this example, we have used the || operator to add a space character between the values Dave and Anderson. This will prevent our values from being squished together.

Instead our result would appear as follows:

'Dave Anderson'

You would more commonly use the || operator to concatenate a space character when you are concatentating multiple fields together.

For example:

SELECT first_name || ' ' || last_name AS customer_name
FROM customers;

This example would return a result set with one column that consisted of the first_name and last_name fields (separated by a space) from the customers table. The column in the result set would be aliased as customer_name.

Concatenate Single Quotes

Since the || operator will concatenate string values that are enclosed in single quotes, it isn't straight forward how to add a single quote character within the result of the concatenated string.

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

For example:

SELECT 'Let''s' || ' learn Oracle'
FROM dual;
Result: 'Let's learn Oracle'

Since our string values 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.

If you wanted to separate out the single quote from the other string values, you could also rewrite this query as follows:

SELECT 'Let' || '''' || 's' || ' learn Oracle'
FROM dual;
Result: 'Let's learn Oracle'