PL/SQL Concatenate

Introduction to PL/SQL Concatenate

The following article provides an outline for PL/SQL Concatenate. In PL/SQL, concatenation is used to join two or more strings in a single string. PL/ SQL allows two ways to perform the concatenation of strings. It can be either done by using the CONCAT function by passing the 2 string values or by using the concatenation operator (||). The difference between the two ways is that the concatenation operator allows any number fnstrings to be passed together using (||) in between them, but the CONCAT function allows only two string values at a time. Thus, concatenation can be done of the strings, numbers, special characters, etc. It is widely used in programs when working with the strings.

Syntax of PL/SQL Concatenate

Given below is the syntax of using the concat operator in order to perform concatenation between two strings in PL/SQL:

CONCAT (string 1, string 2)

Where,

• CONCAT: It is the function which is used in PL/SQL in order to perform concatenation.
• string 1: It is the first string which is to be concatenated.
• string 2: It is the second string which is to be concatenated.

Syntax of performing concatenation in PL/SQL using concatenation operator:

string 1 || string 2

Where,

• ||: It is a concatenation operator used in PL/SQL to perform concatenation.
• string 1: It is the first string which is to be concatenated.
• string 2: It is the second string which is to be concatenated.

How does Concatenation Work in PL/SQL?

• In PL/SQL, concatenation of two strings together is done with the help of the CONCAT function.
• CONCAT function in PL/SQL concatenate only 2 strings at a time; in case the concatenation of multiple strings is required, one needs to nest CONCAT functions or use the concatenation operator (||).
• The CONCAT function returns the string 1 concatenated with the string 2.
• Both the string 1 and string 2 can be of any data type like CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB OR NCLOB.
• Returning the data type of the concatenated string depends on the data type of arguments.
• In the case of different data types of both the arguments passed in the CONCAT function, Oracle returns the data type, which will cause lossless conversion. For example, in CONCAT of CLOB and NCLOB, it will return NCLOB data type; in case of NCLOB and CHAR, it will return NCHAR data type.
• Argument ‘string 1’ is specified as the value to concatenate with the string 2 passed by the user.
• Argument ‘string 2’ is the value which is to be concatenated.

Oracle versions which support the CONCAT function in PL/SQL are given below:

• Oracle 12c
• Oracle 11g
• Oracle 10g
• Oracle 9i
• Oracle 8i

Examples of PL/SQL Concatenate

Different examples are mentioned below:

Example #1

Code:

DECLARE
string1 string(10) := 'Learning ';
string2 string(40) := 'CONCAT function ';
BEGIN
dbms_output.put_line(CONCAT(string1, string2));
END;

Output:

Explanation:

• In the above code, two strings string1 and string2, are declared having the values ‘Learning’ and ‘CONCAT function’, respectively. Then, while printing the output on the console, concatenation of both the strings is done with the CONCAT function’s help and passing both string1 and string2 arguments.
• So the concatenated string ‘Learning CONCAT function’ is printed on the console.

Example #2

Code:

DECLARE
string1 string(10) := 'Hello!!! ';
string2 string(40) := 'Welcome to ';
string3 string(40) := 'Learning PL/SQL';
BEGIN
dbms_output.put_line(CONCAT(CONCAT(string1, string2), string3));
END;

Output:

Explanation:

• In the above code, three strings ‘string1’, ‘string2’ and ‘string3’ are declared in The DECLARE function having the values ‘Hello!!! ‘, ‘Welcome to ‘, ‘Learning PL/SQL’ respectively. As mentioned above, the CONCAT function takes only 2 arguments, and in order to perform concatenation in more than 2 strings, nesting needs to be performed.
• So, to print the concatenated value on the console, a nested CONCAT function is used. The inner statement, (CONCAT(string1, string2), is performed first, and string1 and string2 are concatenated, which results in ‘Hello!!! Welcome to ‘. Outer statement of CONCAT is then executed, which will concatenate the above result with string3. So finally, ‘Hello!!! Welcome to Learning PL/SQL’ is printed on the console.

Example #3

Code:

DECLARE
string1 string(40) := 'Concatenation through ';
string2 string(40) := 'concatenation operator ';
BEGIN
dbms_output.put_line(string1 || string2);
END;

Output:

Explanation:

• In the above code, string1 and string2 are declared in the DECLARE section having the values ‘Concatenation through ‘ and ‘concatenation operator ‘, respectively.
• In the BEGIN block, concatenation is performed using the concatenation operator (||). So, both the variables ‘string1’ and ‘string2’ are written with concatenation operator (||) in between. Finally, the string ‘Concatenation through concatenation operator ‘ is printed on the console as a result.

Example #4

Code:

DECLARE
BEGIN
dbms_output.put_line('Concat' || ' directly');
END;

Output:

Explanation:

• In the above code, instead of passing the string variables having the string to be concatenated, directly string values that are ‘Concat’ and ‘directly’ are passed having the concatenation operator (||) in between them. So the concatenation operator works similarly on passing the values only.
• Finally, ‘Concat directly’ is printed on the console as a result.

Example #5

Code:

DECLARE
string1 VARCHAR2(40) := 'Learn';
string2 VARCHAR2(40) := 'Concatenation';
string3 VARCHAR2(40) := 'in PL/SQL';
result VARCHAR2(100);
BEGIN
result := string1
||' '
|| string2
||' '
|| string3;
dbms_output.put_line(result);
END;

Output:

Explanation:

• In the above code, 4 variables are declared in the DECLARE section of PL/SQL code. In the 4 variables, 3 variables are thee string1, string 2 and string3 having the values ‘Learn’, ‘Concatenation’ and ‘in PL/SQL’ respectively. The Fourth variable is ‘result’ which will be used to store the final concatenated string. In the BEGIN section, strings ‘string1’, ‘string2’ and ‘string3’ are concatenated using the concatenation operator (||) in between the two string values.
• Instead of leaving the space after every string, space ‘ ‘ is also concatenated like a normal string. It means that all the things like numbers, special characters, strings, etc., can be concatenated normally using the concatenation operator. Finally, the output is printed on the console using the ‘result’ variable.

Conclusion

The above description clearly explains what the concatenate function is and how it works in PL/SQL. However, these are the basic functions commonly used in any programming language, so it is important for the programmer to understand them better to use them easily according to the requirements of the code.

Recommended Articles

This is a guide to PL/SQL Concatenate. Here we discuss the introduction, how does concatenation work in PL/SQL? and examples. You may also have a look at the following articles to learn more –

1. Triggers in PL/SQL
2. PL/SQL Collections
3. PL/SQL Data Types
4. Loops in PL/SQL