EDUCBA

EDUCBA

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

PLSQL Replace

Home » Software Development » Software Development Tutorials » Software Development Basics » PLSQL Replace

PLSQL Replace

Definition of PLSQL Replace

PL/ SQL REPLACE is a function which allows the replacement of character string with another set of character strings. This function is widely used while programming in case of some spelling mistakes of the data. Instead of deleting the data and interesting again, REPLACE can be used which will modify the data according to the specific requirements. The REPLACE function takes 3 parameters in which one parameter is Optional. It also works great in case of removal of string characters from the particular input string expression. As this function works on the strings, the return type is also a string type but the char set depends on the input string data type.

Syntax:

Start Your Free Software Development Course

Web development, programming languages, Software testing & others

Below given is the syntax of PL/SQL Replace function:

REPLACE (string1, string_to_replace [, replacement_string])

where,

  • string1: The string or expression where the sequence of characters needs to be replaced with other characters. This is basically an input string.
  • string_to_replace: The string which will be searched in string1 and will be replaced in the expression.
  • replacement_string: It is an Optional parameter. It is a replacement string. All the occurrences of ‘string_to_replace’ will be replaced with ‘replacement_string’. If the replacement_string is not present in the string expression, all the occurrences of ‘replacement_string’ are removed from the string1.
  • Return type: The REPLACE function returns the string value to the user.

How does the PLSQL Replace Function Work?

Below given are some of the important points which needs to keep in mind related to the working of REPLACE function in PL/SQL:

  1. The REPLACE function of PL/SQL is used to replace a sequence of string with another set of string/ characters.
  2. It accepts 3 parameters in which 1 parameter is Optional , i.e. string1( input string), string_to_replace (string to be searched), replacement_string (Optional. replacing string).
  3. In case when the string_to_replace( string to be searched) is null, it will return the same string1 (input string expression).
  4. All the parameters, i.e. string1, string_to_replace and replacement_string can be of any data type, i.e. CHAR, VARCHAR2, NVARCHAR2, NCHAR, CLOB. The resulting string is of the same character set as char.
  5. The REPLACE function of PL/ SQL provides the functionality somewhat similar to that of TRANSLATE function, only difference being that TRANSLATE allows one-to-one substitution whereas REPLACE allows replacement of string/ characters, even removal of character strings (in case of omission of Optional parameter).
  6. If the replacement_string is not present in the REPLACE function of PL/ SQL, it works for the removal of string characters. It removes all the occurrences of string_to_replace in the string1. Function returns the removed characters string.

Supported versions Oracle which supports the REPLACE function in PL/ SQL are given below:

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

Examples of PLSQL Replace

Some of the examples of PL/ SQL showing the implementation of REPLACE function in the code are given below:

Example #1

Code:

DECLARE
Tst_String string(25) := 'hellohello';
BEGIN
dbms_output.put_line(REPLACE(Tst_String, 'h'));
END;

Output:

PLSQL Replace-1.1

Explanation: In the above code, string1 or the string expression is ‘hellohello’. REPLACE function used above has 2 parameters, i.e. ‘Tst_String’ which has a string expression and the string_to_replace is ‘h’. There is no replacement_string in the above REPLACE function. So, first, the string ‘hi’ is searched in the string expression and will be removed from it. So, the resulting string expression is ‘elloello’ removing all the occurences of ‘h’.

Example #2

DECLARE
Tst_String string(25) := 'hello how are you';
BEGIN
dbms_output.put_line(REPLACE(Tst_String, 'hello', 'hi'));
END;

Popular Course in this category
Sale
Software Testing Training (9 Courses, 2 Projects)9 Online Courses | 2 Hands-on Projects | 60+ Hours | Verifiable Certificate of Completion | Lifetime Access
4.5 (8,180 ratings)
Course Price

View Course

Related Courses
Selenium Automation Testing Training (9 Courses, 4+ Projects, 4 Quizzes)Appium Training (2 Courses)JMeter Testing Training (3 Courses)

Output:

PLSQL Replace-1.2

Explanation: In the above code, string1 or the string expression is ‘hello how are you’. The REPLACE function used above has 3 parameters ‘Tst_String’ which has a string expression, string_to_replace is ‘hello’ and the replacement_string is ‘hi’. Since ‘hello’ is present 1 time in the expression, it would be replaced with ‘hi’ in the input expression. So, the resulting string expression is ‘hi how are you’ in the above code.

Example #3

Code:

DECLARE
Tst_String string(25) := 'hello how are you';
BEGIN
dbms_output.put_line(REPLACE(Tst_String, 'are', '  '));
END;

Output:

PLSQL Replace-1.3

Explanation: In the above code, string1 or the string expression is ‘hello how are you’. The REPLACE function used above has the input parameter Tst_String (which is string1), string_to_replace is  ‘are’ and the replacement_string is ‘  ‘ (two blank spaces). So in the string expression, ‘are’ is replaced with the blank spaces. So, the resulting string is ‘hello how you’ in the code mentioned above.

Example #4

Code:

DECLARE
Tst_String string(25) := 'hi how are you hi';
BEGIN
dbms_output.put_line(REPLACE(Tst_String, 'hi'));
END;

Output:

Output-1.4

Explanation: In the above code, string1 or the string expression is ‘hi how are you hi’. So in the function ‘REPLACE’, there are 2 parameters, Tst_String which is string1 and the string_to_replace is ‘hi’. There is no replacement_string parameter written above, so ‘hi’ is removed from the expression (nothing would be replaced). So the resulting string is    ‘   how are you   ‘ replacing all the ‘hi’ from the string expression.

Example #5

Code:

DECLARE
Tst_String string(25) := 'Congratulations to our world';
BEGIN
dbms_output.put_line(REPLACE(Tst_String, 'tu', 'ih'));
END;

Output:

Output-1.5

Example #6

 Code:

DECLARE
Tst_String string(25) := 'Congratulations to our world';
BEGIN
dbms_output.put_line(REPLACE(Tst_String, 'tua', 'ih'));
END;

Output:

Output-1.6

Explanation: In the above code, ‘Congratulations to our world’ is the string expression, ‘tua’ is the ‘string_to_replace’ and ‘ih’ is the ‘replacement_string’. So, in the above string expression, there is no string ‘tua’ present. So, if the searchable string is not present in the expression, the resulting string expression remains the same as the string 1. There would be no change in the input string expression.So, the resulting string in the above code would be ‘Congratulations to our world’.

Conclusion

The above description clearly explains what the REPLACE function is and how it works in PL/SQL. Though Oracle also provides the TRANSLATE function which works similar to that of REPLACE only one-to-one substitution is allowed in it whereas REPLACE allows the substitution of a sequence of the whole string. For a programmer, it is important to understand both of them and the difference between them to have a clear understanding.

Recommended Articles

This is a guide to PLSQL Replace. Here we also discuss the definition and how does the PLSQL Replace function works? along with different examples and its code implementation. You may also have a look at the following articles to learn more –

  1. Python Doubly Linked List
  2. Deque in Python
  3. Linear Search in Python
  4. Shell sort in Python

All in One Software Development Bundle (600+ Courses, 50+ projects)

600+ Online Courses

50+ projects

3000+ Hours

Verifiable Certificates

Lifetime Access

Learn More

0 Shares
Share
Tweet
Share
Primary Sidebar
Software Development Basics
  • Basics
    • Types of Algorithms
    • Vue.js? nextTick
    • Vue.js Transition
    • Page Replacement Algorithms
    • What is CLI
    • Open Source Software
    • Solve Problems With Technology (Simple)
    • What is Application Software & Types
    • SSRS Dashboard
    • SSRS Opinion Panel
    • Microsoft Word Alternative
    • ADDIE Model
    • V-model advantages and disadvantages
    • Gatsby Plugins
    • Putty version
    • Xampp versions
    • Avro converter
    • Entity Framework Core
    • Gulp File Include
    • Gulp Autoprefixer
    • Gulp Terser
    • System Software Tools
    • Typography App
    • Software as a Service (Saas)
    • Icon Font Pack
    • Interpret Results Using ANOVA Test
    • Blogging Insights Your Analytics
    • Increase Productivity Technology
    • Free Multimedia Software
    • Information Technology Benefits
    • What is SPSS and How Does It Work
    • Learn to Code For Beginners (Advance)
    • Uses of Coding
    • Uses Of Raspberry Pi
    • What Is System Design
    • Introduction to NLP
    • What is MapReduce
    • What is SoapUI
    • What is MVC
    • What is Multithreading
    • What is Elasticsearch
    • Elasticsearch Features
    • What is Neural Networks
    • What is Swift
    • What is PLC
    • What is Open Cart
    • What is Mainframe
    • What is JMS
    • What is Cognos
    • What is Open Source
    • What is Bot
    • What is SOAP
    • What is COBOL
    • What is GraphQL
    • What is Microcontroller
    • What is Open-Source License
    • What is Visual Studio Code
    • What is Pandas
    • What is Hypervisor
    • What is Common Gateway Interface
    • What is IDE?
    • What is SSRS?
    • What is MVC Design Pattern
    • What is Application Server
    • What is GPS
    • What is Botnet
    • What is Assembly Language
    • System Analysis And Design
    • HTTP Caching
    • What is Buffer Overflow
    • What is Ajax
    • What is Joomla
    • What is Appium
    • What is SVN
    • What is SPSS
    • What is WCF
    • What is Groovy
    • What is Clickbait
    • What is SOA
    • What is GUI
    • What is FreeBSD
    • What is WebSocket
    • What is WordPress
    • What is OSPF
    • What is Coding
    • What is Raspberry Pi
    • HTTP Cookies
    • What is Hub?
    • What is Bridge
    • What is Switch
    • What is Internet Application
    • What is Sensors
    • What is Proximity Sensors
    • What is Full Stack
    • System Design Interview Questions
    • What is Salesforce technology
    • What is Salesforce Sales Cloud
    • What is OOP
    • What is CMD
    • What is React
    • React Redux Typescript
    • What is DSS
    • What is SVG
    • SVG File
    • Bash Sleep Command
    • What is MTU
    • What is Apex
    • What is Desktop Software
    • Tor Browser, Anonymity and Other Browsers
    • Avoid Pitfalls of Shadow IT
    • Freelance Web Graphic Designer
    • What is Storage Virtualization
    • What is Web Services?
    • What is Social Networking?
    • What is Microservices Architecture?
    • Microservices Tools
    • Advantages of Microservices
    • Uses of Internet
    • Software Platforms
    • Uses of Internet for Business
    • Architecture of Web Services
    • Web Application Testing
    • Advantages of Web Service
    • CPU Virtualization
    • Types of Web Services
    • Web Services Testing
    • What is RabbitMQ?
    • RabbitMQ Architecture
    • Advantages of Bitcoin
    • Penetration Testing Services
    • Puppet Alternatives
    • What is Memcached?
    • What is Browser?
    • Types of Satellites
    • Model Driven Architecture
    • Types of Variables in Statistics
    • Integration Architecture
    • What is API Integration?
    • What is Grid Computing?
    • Asus File Manager
    • What is GPRS?
    • What is Gradle?
    • What is Basecamp?
    • Software System Architecture
    • GSM Architecture
    • What is Nagios?
    • AppDynamics Tool
    • Logical Architecture
    • What is Microsoft Planner
    • What is Circuit Switching
    • What is ARM?
    • Embedded Control Systems
    • Embedded System Programming
    • Embedded System Development
    • Embedded Systems Software
    • Embedded System Project
    • Types of Embedded Systems
    • What is Bitbucket?
    • Requirement Engineering
    • Types of Engineering
    • What is WAP
    • What is Registry?
    • What is Dynatrace?
    • What is Digital Forensics?
    • Hardware Virtualization
    • AppDynamics Careers
    • Bandwidth Monitoring Tools
    • Ping Monitor Tools
    • Dynatrace Tools
    • What is Trello?
    • What is AppDynamics?
    • What is Remote Desktop?
    • What is Extranet?
    • What is LTE Network?
    • What is Firebase?
    • Website Monitoring Tool
    • Number Systems
    • Service Desk Manager
    • Static Website
    • Dynamic Website
    • What is Email?
    • What is URL Link?
    • What is Program?
    • What is Lock Screen?
    • What is Grafana
    • Unguided Media Transmission
    • IT Governance
    • IT Governance Framework
    • Remote Support Softwares
    • What is Unification?
    • Topological Map
    • What is LAMP?
    • USB Flash Drive
    • Software Development Models
    • Digital Circuit
    • What is Webpack?
    • Fault Tolerance
    • What is DSL Modem?
    • What is Mozilla Firefox?
    • What is Vagrant?
    • Types of Research Methodology
    • Grafana Plugins
    • Ionic Components
    • Nginx Version
    • RabbitMQ Routing Key
    • CakePHP
    • Telegram Features
    • What is CDN
    • RethinkDB
    • Symfony Version
    • UWP
    • cPanel version
    • What is assembly?
    • Seed7
    • Switching Techniques
    • OCaml
    • Pseudocode?Algorithm
    • Quality Control Methods
    • What is OneNote?
    • Workstation Uses
    • Soft Computing Techniques
    • Remote Access Software
    • Remote Desktop Tools
    • OneNote Shortcuts
    • Software Review
    • What is Qubit?
    • Static Analysis Tools
    • Register in Microprocessor
    • What is VDI?
    • What is Svelte?
    • RabbitMQ Version
    • Groovy Version
    • Code Walkthrough
    • What is Telegram?
    • Gradle Version
    • What is Recycle Bin?
    • What is Cordova?
    • Swagger version
    • Doxygen
    • Phalcon
    • Metasploit Framework
    • Microsoft Word Shortcut Keys
    • Wordpad shortcut keys
    • Burp Suite
    • Google Docs Shortcuts
    • Install VPN
    • Frontend Challenges
    • CodeIgniter Version
    • VMware Tools
    • CDMA Advantages
    • CDMA Uses
    • Servlet Session Management
    • Log4j Version
    • Remote Desktop Softwares
    • Soapui Load Test
    • Scikit Learn Version
    • VMware Benefits
    • Google Slides Shortcuts
    • What is XAMPP?
    • What is PyGTK?
    • VMware Fusion
    • What is cPanel?
    • Ubuntu Version
    • Server Types
    • App Analytics Tools
    • DNS Types
    • Evernote Features
    • Restful architecture
    • GNOME Keyboard Shortcuts
    • AngelScript
    • NativeScript Layouts
    • PowerPoint Version
    • setInterval Function
    • Shopify Apps
    • TypeScript foreach loop
    • Socio Technical System
    • PowerPoint Shortcut Keys
    • Civil Engineering Tools
    • OpenLayers vs Leaflet
    • Circuit Switching Advantages and Disadvantages
    • LotusScript
    • Multiplexer
    • Types of Broadband
    • What is Standardization
    • Methods of Development
    • Software Requirement Specification
    • CentOS restart network
    • Bouncy numbers
    • Burp suite proxy
    • Redshift window functions
    • Mesh Topology Advantages and Disadvantages
    • What is Zabbix?
    • Test Techniques
    • Test Development
    • What is PyCharm
    • What is REST
    • JDBC version
    • System software features
    • Ableton versions
    • Unreal engine version
    • RAD advantage disadvantage
    • Incremental Model Advantage and Disadvantage
    • Disadvantages of Internet
    • What is VoIP
    • WAP Architecture
    • CentOS unzip
    • Cubase Shortcuts
    • Cubase Versions
    • Libreoffice shortcut keys
    • Archiving Software
    • Layered Architecture
    • Coverage Types
    • What is Kivy?
    • Types of Methodology
    • Swift JSON
    • JSON Serialize
    • TypeScript?boolean
    • TypeScript keyof object
    • TypeScript RegEx
    • TypeScript?date
    • TypeScript object
    • CentOS Version
    • XSLT if else
    • Binary Search JavaScript
    • Binary search with recursion
    • Bitbucket Pull Request
    • Bitbucket Add SSH Key
    • Bitbucket Jenkins
    • PLSQL Replace
    • Evernote Notes
    • Rust vs Python
    • Test Scenario
    • Deadlock in Operating System
    • MVVM Architecture
    • What is Keyboard
    • WordPress Hosting
    • Software requirement
    • CentOS Add User to Group
    • Backup Types
    • Firewall Rules
    • Microprocessor Features
    • Maven Versions
    • OneNote features
    • Binary search tree insertion
    • Quick sort algorithm
    • B+ tree insertion
    • What is Automation?
    • What is Digital Electronics?
    • Wireless Transmission Media
    • Border Gateway Protocol
    • Email Encryption Software
    • Endpoint Encryption
    • Outlook Alternative
    • What is Abacus
    • Encapsulation Benefits
    • FL Studio Keyboard Shortcuts
    • NordVPN Features
    • Statsmodels API
    • Statsmodels Linear Regression
    • Buzz number
    • Krishnamurthy Number
    • What is Compact Disc?
    • Bucket Sort Algorithm
    • Insertion Sort Algorithm
    • Redis Version
    • Chatbot Benefits
    • Full Stack Technologies
    • Civil Engineering Types
    • Tomcat Web Server
    • Bitbucket Branching Strategy
    • Anaconda Navigator
    • UML Class Diagram
    • System Monitoring Tool
    • Drupal Features
    • System Software Functions

Related Courses

Software Testing Training

Selenium Training Certification

Appium Training

JMeter Certification Training

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
  • Java Tutorials
  • Python Tutorials
  • All Tutorials
Certification Courses
  • All Courses
  • Software Development Course - All in One Bundle
  • Become a Python Developer
  • Java Course
  • Become a Selenium Automation Tester
  • Become an IoT Developer
  • ASP.NET Course
  • VB.NET Course
  • PHP Course

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

EDUCBA
Free Software Development Course

Web development, programming languages, Software testing & 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 Software Development Course

Web development, programming languages, Software testing & 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 - Software Testing Training Learn More