×
☰ See All Chapters

Oracle REPLACE Function

Oracle REPLACE function searches a string for a specified substring and returns a new string where the specified substring is replaced.

Oracle REPLACE Function Syntax

REPLACE(CHARACTERSET, VALUE1, VALUE2)

There are normally three arguments with the REPLACE function:

CHARACTERSET: The string to be converted. This can be an expression or a direct value or a value from a column of any type.

VALUE1:  list of the characters to convert.

VALUE2: list of the substitution characters.

Oracle REPLACE Function Example

Creating table for demonstrating REPLACE Function

CREATE TABLE NAME

(

  FNAME  VARCHAR(10 )    NOT NULL,

  LNAME  VARCHAR(10 )

);

Insert into NAME (FNAME, LNAME) Values ('ADI', 'TEMP');

Insert into NAME (FNAME, LNAME) Values ('NAVEEN', 'SHETTY');

Insert into NAME (FNAME, LNAME) Values ('ARJUN', 'SHETTY');

Insert into NAME (FNAME, LNAME) Values ('HARISH', 'GOWDA');

Insert into NAME (FNAME, LNAME) Values ('HARI', 'PRASAD');

Insert into NAME (FNAME, LNAME) Values ('ARJUN', 'SHETTY');

Insert into NAME (FNAME, LNAME) Values ('KIRAN', 'KUMAR');

COMMIT;

Example 1

SELECT FNAME, REPLACE(LNAME, 'SHETTY', 'SETTY')

FROM NAME

oracle-replace-function-0

Example 2

SELECT  CONCAT(REPLACE(FNAME, 'HARI', 'GIRI'), REPLACE('SHETTY123', '123', ''))

FROM NAME

oracle-replace-function-1

All Chapters
Author