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;
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.