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
Leave a comment