Different uses of REPLACE function in Oracle are shown here.

Example-1: use of REPLACE in the string

SELECT
REPLACE( 'Oracle is a relational database system', 'Oracle', 'MySQL' ) Replaced_text
FROM
dual;

Output:

Example-2: use of REPLACE in the table

create the post table with the following structure:

CREATE TABLE post (
post_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
title VARCHAR2( 255 ),
post_body VARCHAR2(4000),
PRIMARY KEY (post_id)
);

Insert some sample data into the post table:

INSERT INTO post( title, post_body)
VALUES('First Post','It is a <em> technology</em> based post');

After that, suppose you want to replace all <strong> tags with <b> tags in the article_body column. Here are the queries to do so:

UPDATE
post
SET
post_body = REPLACE( post_body, '<em>', '<strong>' );
UPDATE
post
SET
post_body = REPLACE( post_body, '</em>', '</strong>' );

Visit the following video for the explanation.

Exercise: Create a table named customers (id, name, address, mobile, district) with some data. Search those customer records who are from Dhaka and highlight those customer names by red color.