In Oracle, to join or concatenate two strings we use the CONCAT function. The following is the syntax of the Oracle Concat function:

Oracle Concat Syntax

concat(a, b)

Here it will join the string a and b and will return a new string. Below are the examples:

Oracle Concat Function Examples

The following is the Oracle SQL query to join two strings using the Concat function:

select concat('Oracle', 'Corporation') from dual;

Output:

OracleCorporation

But here you can see that there is no space between these two strings. To add the space you have to add the space on the right side of 'Oracle ' or at the left side of ' Corporation'. Because using the Concat function you can pass only two strings.

To concatenate multiple strings in Oracle, you can use || (2 pipes) operator. Below is an example:

select 'Oracle' || ' '|| 'Corporation' a_string from dual;

Output:

Oracle Corporation

Concatenate Strings and numbers together:

select 'Oracle' || ' '|| 'Corporation founded in' || ' ' || 1977 a_string from dual;

Oracle Corporation founded in 1977

Using Concat Function in PL/SQL Block

In the below PL/SQL example, it declares a variable named v_string and concatenate the two strings 'abc' and 'xyz' and print the single string.

Declare
   v_string varchar2(100);
Begin
   v_string := concat('abc', 'xyz');
   dbms_output.put_line(v_string);
End;

Output:

abcxyz

Using || (PIPES) to Concatenate in PL/SQL

Declare
   v_string varchar2(100);
Begin
   v_string := 'abc' || 'xyz' || '123' || '456';
   dbms_output.put_line(v_string);
End;

Output:

abcxyz123456