Oracle REPLACE() Function

The Oracle (PL/SQL) REPLACE() function returns the given string with every occurrences of a specified substring replaced with a new substring. If the replacement_string is omitted or null, then all occurrences of search_string are removed. If search_string is null, then the given string is returned.

Syntax

REPLACE(string, search_string, replacement_string)

Parameters

Return Value

Returns the string with the replaced values.

Example 1:

The example below shows the usage of REPLACE() function.

REPLACE('SQL Tutorial', 'SQL', 'HTML')
Result: 'HTML Tutorial'

REPLACE('xyz xyz', 'xyz', 'abc')
Result: 'abc abc'

REPLACE('100', '0', '5')
Result: '155'

REPLACE(100, 0, 5)
Result: '155'

REPLACE('xyz xyz', 'z')
Result: 'xy xy'

Example 2:

Consider a database table called Employee with the following records:

In the query below, the REPLACE() function is used to create a new column called NewEmpID by replacing 'AXZ' with 'XAY' from all records of EmpID column value.

SELECT Employee.*, 
REPLACE(EmpID, 'AXZ', 'XAY') AS NewEmpID 
FROM Employee;

This will produce the result as shown below: