EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login

PL/SQL Concatenate

Home » Data Science » Data Science Tutorials » Database Management Tutorial » PL/SQL Concatenate

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

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

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:

PLSQL Concatenate 1

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:

PLSQL Concatenate 2

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;

Popular Course in this category
Sale
SQL Training Program (7 Courses, 8+ Projects)7 Online Courses | 8 Hands-on Projects | 73+ Hours | Verifiable Certificate of Completion | Lifetime Access
4.5 (8,923 ratings)
Course Price

View Course

Related Courses
PL SQL Training (4 Courses, 2+ Projects)Oracle Training (14 Courses, 8+ Projects)

Output:

PLSQL Concatenate 3

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:

Concat Directly

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:

statement processed

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

All in One Data Science Bundle (360+ Courses, 50+ projects)

360+ Online Courses

50+ projects

1500+ Hours

Verifiable Certificates

Lifetime Access

Learn More

0 Shares
Share
Tweet
Share
Primary Sidebar
Database Management Tutorial
  • PL/SQL
    • What is PL/SQL?
    • Careers in PL/SQL
    • PLSQL procedure
    • PL/SQL Exception
    • PL/SQL LIKE
    • PL/SQL Raise Exception
    • PLSQL rowtype
    • PLSQL? bind variables
    • PL/SQL Record
    • PL/SQL WITH
    • PL/SQL bulk collect
    • PL/SQL Block Structure
    • PL/SQL else if
    • PL/SQL nvl2
    • PL/SQL Package
    • PL/SQL exists
    • PL/SQL instr
    • PL/SQL listagg
    • PL/ SQL Formatter
    • PLSQLlength
    • PL/SQL Commands
    • PL/SQL Data Types
    • CASE statement in PL/SQL
    • PL/SQL IF Statement
    • Loops in PL/SQL
    • PL/SQL Add Column
    • For Loop in PLSQL
    • PL/SQL Cursor Loop
    • PLSQL Array
    • Cursors in PL/SQL
    • PL/SQL FOR Loop Cursor
    • PL/SQL Queries
    • PL/SQL SELECT INTO
    • PL/SQL TO_CHAR
    • PL/SQL UNION
    • PL/SQL NOT EQUAL
    • PL/SQL varray
    • PL/SQL Concatenate
    • PL/SQL UPDATE
    • PL/SQL TRIM
    • PL/SQL GROUP BY
    • PL/SQL GOTO
    • PL/SQL Date Functions
    • PL/ SQL having
    • PL/SQL to_DATE
    • PL/SQL NVL
    • PLSQL format date
    • PLSQL mod
    • PLSQL round
    • PL/SQL Boolean
    • PL/SQL exit
    • PL/SQL DECODE
    • PL/SQL ROWNUM
    • PLSQL?pivot
    • PLSQL string functions
    • PL/SQL Block
    • PL/SQL Function
    • PL/SQL Unwrapper
    • PL/SQL Table
    • PL/SQL ALTER TABLE
    • PLSQL execute immediate
    • Triggers in PL/SQL
    • PL/SQL Collections
    • PL/SQL stored procedure
    • PL/SQL Anonymous Block
    • PLSQL Interview Questions
  • DataBase Management
    • Text Data Mining
    • Roles of Database Management System in Industry
    • SQL Server Database Management Tools
    • Database administrator skills
    • Database Management Systems Advantages
    • Database Testing Interview Questions
    • Data Administrator
    • Database Administrator
    • Database Management Software
    • DataStage
    • Types of Database Models
    • Types of Database
    • Hierarchical Database Model
    • Relational Database
    • Relational Database Advantages
    • Operational Database
    • What is RDBMS?
    • What is DB2?
    • Data Masking Tools
    • Database Security
    • Data Replication
    • Bitmap Indexing
    • Second Normal Form
    • Third Normal Form
    • Fourth Normal Form
    • Data Definition Language
    • Data Manipulation Language
    • Data Control Language
    • Transaction Control Language
    • Conceptual Data Model
    • Entity-Relationship Model
    • Relational Database Model
    • Sequential File Organization
    • Checkpoint in DBMS
    • Teradata Create Table
    • Centralized Database
    • Data Storage in Database
    • Thomas write Rule
    • DBA Interview Questions
    • What is JDBC?
    • jdbc hive
    • Apriori Algorithm
    • JDBC Architecture
    • JDBC Interview Questions
    • Wildcard Characters
    • Distributed Database System
    • Multidimensional Database
  • TSQL Basic
    • TSQL
    • What is T-SQL
    • T-SQL Commands
    • T-SQL String Functions
    • TSQL Interview Questions
  • MariaDB
    • MariaDB Versions
    • MariaDB?list users
    • MariaDB Commands
    • MariaDB odbc
    • MariaDB Workbench
    • MariaDB for windows
    • MariaDB Server
    • MariaDB? Data Types
    • MariaDB?boolean
    • MariaDB phpMyAdmin
    • MariaDB Mysqldump
    • MariaDB Java Connector
    • MariaDB insert
    • MariaDB UPDATE
    • MariaDB? rename column
    • MariaDB AUTO_INCREMENT
    • MariaDB Timezone
    • MariaDB GROUP_CONCAT
    • MariaDB wait_timeout
    • MariaDB MaxScale
    • MariaDB? with
    • MariaDB? create?table
    • MariaDB? SHOW TABLES
    • MariaDB alter table
    • MariaDB List Tables
    • MariaDB JSON Functions
    • MariaDB Foreign Key
    • MariaDB? trigger
    • MariaDB Grant All Privileges
    • MariaDB Select Database
    • MariaDB? create database
    • MariaDB Delete Database
    • MariaDB List Databases
    • MariaDB Functions
    • MariaDB? TIMESTAMP
    • MariaDB create user
    • MariaDB add user
    • MariaDB show users
    • MariaDB Delete User
    • MariaDB? change user password
    • MariaDB? change root password
    • MariaDB reset root password
    • MariaDB IF
    • MariaDB bind-address
    • MariaDB Transaction
    • MariaDB Cluster
    • MariaDB Logs
    • MariaDB Encryption
    • MariaDB? backup
    • MariaDB Replication
    • MariaDB max_allowed_packet
    • MariaDB? performance tuning
    • MariaDB export database
    • MariaDB? import SQL
  • SQLite
    • What is SQLite
    • SQLite Commands
    • SQLite Data Types
    • SQLite COUNT
    • SQLite Boolean
    • SQLite autoincrement
    • SQLite select
    • SQLite? Bulk Insert
    • SQLite? add column
    • SQLite? concat
    • SQLite BETWEEN
    • SQLite group by
    • SQLite CASE
    • SQLite group_concat
    • SQLite array
    • SQLite? enum
    • SQLite sum
    • SQLite create table
    • SQLite Alter Table
    • SQLite Create Database
    • SQLite Delete
    • SQLite connection string
    • SQLite Database
    • SQLite Describe Table
    • SQLite Show Tables
    • SQLite exit
    • SQLite create index
    • SQLite foreign key
    • SQLite Stored Procedures
    • SQLite Extension
  • DB2
    • DB2? current date
    • DB2 purescale
    • DB2 backup
    • DB2 restore
    • DB2 C Express
    • DB2 Version
    • DB2? Architecture
    • DB2? Data Types
    • DB2? load
    • DB2? order by
    • DB2 date
    • DB2 NVL
    • DB2? update
    • DB2 warehouse
    • DB2 grant
    • DB2 database
    • DB2 VARCHAR
    • DB2? INSERT
    • DB2 LISTAGG
    • DB2 LIKE
    • DB2 TRUNCATE TABLE
    • DB2 LIST TABLES
    • DB2 between
    • DB2? current timestamp
    • DB2? length
    • DB2? bind
    • DB2 limit rows
    • DB2? export
    • DB2 with
    • DB2 Create Table
    • DB2 case statement
    • DB2 CAST
    • DB2 Functions
    • DB2 Date Functions
    • DB2? row_number
    • DB2 trim
    • DB2? Translate
    • DB2 UNION
    • DB2 timestamp
    • DB2? TIMESTAMPDIFF
    • DB2? replace
    • DB2 merge
    • DB2 COALESCE
    • DB2 ISNULL
    • DB2? explain
    • DB2 Join
    • DB2 alter column
    • DB2 rename column
    • DB2? Describe Table
    • DB2? rename table
    • DB2 List Databases
    • DB2 LUW
    • DB2 Query
    • DB2 GROUP BY
    • DB2 TO_DATE
    • View Serializability in DBMS
    • MariaDB Join
    • MariaDB JSON
    • MariaDB? show databases
    • Dataset Normalization
    • MariaDB Max Connections
    • jdbc connection
    • MariaDB GUI
  • DBMS
    • Introduction To DBMS
    • DBMS ER Diagram
    • What is DBMS?
    • DBMS join
    • DBMS Functions
    • Data Administrator in DBMS
    • DBMS Canonical Cover
    • DBMS Log-Based Recovery
    • DBMS Multivalued Dependency
    • Netezza Database
    • DBMS Concepts
    • DBMS Constraints
    • DBMS_Scheduler
    • B+ Tree in DBMS
    • DBMS_LOB
    • dbms entity
    • DBMS Foreign Key
    • DBMS Users
    • DBMS_Metadata.get_ddl
    • Relational Algebra in DBMS
    • DBMS Components
    • DBMS Features
    • DBMS Models
    • DBMS Relational Model
    • Hashing in DBMS
    • DBMS network model
    • Relationship in DBMS
    • ER Model in DBMS
    • Data Models in DBMS
    • Static Hashing in DBMS
    • Advantages of DBMS
    • dbms_output.put_line
    • DBMS Data Dictionary
    • dbms_xplan.display_cursor
    • Normal Forms in DBMS
    • DBMS helps achieve
    • DBMS 3 tier Architecture
    • Relational Calculus in DBMS
    • Serializability in DBMS
    • File Organization in DBMS
    • DBMS Transaction Processing
    • States of Transaction in DBMS
    • Functional Dependency in DBMS
    • Generalization in DBMS
    • Data Independence in DBMS
    • Lock Based Protocols in DBMS
    • Deadlock in DBMS
    • Integrity Constraints in DBMS
    • Concurrency Control in DBMS
    • Validation Based Protocol in DBMS
    • DBMS Locks
    • Normalization in DBMS
    • Transaction Property in DBMS
    • Specialization in DBMS
    • Aggregation in DBMS
    • Types of DBMS

Related Courses

SQL Certification Course

PL/SQL Certification Course

Oracle Certification Course

Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Special Offer - SQL Certification Course Learn More