Home » Oracle SQL » Oracle REPLACE

Oracle REPLACE

REPLACE is a string function used in Oracle to find a ‘text value1’ in expression or column and replace it with ‘text value2’.

Syntax

The Syntax of REPLACE function is as follows:

REPLACE( Expression, text_value1 [,text_value2])

Here,

Expression – Expression could be a string or column name. We make changes to this expression using REPLACE function.

text_value1 – This is the value that needs to be replaced in the expression. If text_value1 is NULL or empty then Oracle returns the expression with no changes.

text_value2 – This is the value that will replace text_value1.

If we don’t give text_value2, then Oracle will simply remove all instances of text_value1 from the expression.

Lets understand this through some examples.

Oracle REPLACE Examples

The following query is used for replacing ‘Anyone’ in a string to ‘Everyone’.

SELECT
REPLACE( 'Anyone can learn SQL.', 'Anyone', 'Everyone' )
FROM
dual;

Result:

The query replaces the string ‘Anyone’ with ‘Everyone’.

For the next example, lets create a dummy table named ‘Friends’. It has the columns friend_id, name and age.

The query to create the ‘Friends’ table is as follows:

CREATE TABLE friends(
friend_id NUMBER,
name VARCHAR2( 255 ),
age NUMBER,
PRIMARY KEY (friend_id)
);

Let’s insert some dummy data into our table on which we will further perform operations:

INSERT INTO friends( friend_id, name, age)
VALUES(1,'**Ross!!',25);

INSERT INTO friends( friend_id, name, age)
VALUES(2,'**Phoebe!!',26);

INSERT INTO friends( friend_id, name, age)
VALUES(3,'**Monica!!',24);

INSERT INTO friends( friend_id, name, age)
VALUES(4,'**Joey!!',25);

INSERT INTO friends( friend_id, name, age)
VALUES(5,'**Chandler!!',26);

INSERT INTO friends( friend_id, name, age)
VALUES(6,'**Rachel!!',24);

Now, that we have created the Friends and inserted the dummy data. Let’s see how it looks:

Result:

Let’s use the following query to replace the characters ‘*’ and ‘!’ with empty string.

UPDATE friends
SET name = REPLACE ( name, '**','');

UPDATE friends
SET name = REPLACE ( name, '!!','');

Result:

Let’s see the updated table using simple SELECT query.

SELECT * FROM friends;

Result:

Note:

Oracle also has a TRANSLATE function which works the same way REPLACE does. The only difference being that it is used for substituting a single character with another character.

In this tutorial, we learned how Oracle REPLACE function is used for replacing a string with another string or set of characters.

<< Previous
RPAD

Next >>
ROUND