This article is written about how to use the Oracle/PLSQL || concatenate operator with syntax and examples.
The Oracle/PLSQL || operator allows you to concatenate 2 or more strings together.
The syntax for the || operator in Oracle/PLSQL is:
string1 || string2 [ || string_n ]
Parameters or Arguments
The first string to concatenate.
The second string to concatenate.
Optional. The nth string to concatenate.
The || operator returns a string value.
The || operator can be used in the following versions of Oracle/PLSQL:
Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i
Let’s look at some concatentaion examples and explore how to use the || operator in Oracle/PLSQL.
'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.
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:
You would more commonly use the || operator to concatenate a space character when you are concatentating multiple fields together.
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.
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'