How to Concatenate Strings in Oracle

Oracle offers two ways to concatenate strings. The first uses the || operator:

select 'Join these ' || 'strings with a number ' || 23 
from dual;

result
Join these strings with a number 23

You can see from above that Oracle took care of transforming the number to a string to attach it to the rest. Note that you also need to consciously add spaces to make the string readable. For example, in the customer table to join the first and last names of the customers you have to add a single space in the concatenation:

select first_name||' '||last_name as customer_name from customer limit 5;

customer_name
MARY SMITH
PATRICIA JOHNSON
LINDA WILLIAMS
BARBARA JONES
ELIZABETH BROWN

When concatenating strings Oracle considers the NULL value as the empty string, then any concatenation expression will return a value even when one element in the expression is NULL.

select 'Null concatenated with ||'||' something will return ' || 'something' || null 
from dual;

result
Null concatenated with || something will return something

You can use concat instead of || operator in Oracle:

select concat('Concat() works', ' identical to || but only supports two strings')
from dual;
 
concat
Concat() works identical to || but only supports two strings

In all the previous examples we used a table called dual, this is because in Oracle the from clause is mandatory. The dual table is a pseudo-table you can use in any query when you want to calculate an expression not based on table columns.

IN THIS PAGE