MySQL REPLACE() Function

The MySQL REPLACE() function replaces all occurrences of a specified string.

Note: This function performs a case-sensitive replacement.

Syntax

REPLACE(string, from_substring, to_substring)

Parameters

`string`	`Required.` Specify the source string.
`from_substring`	`Required.` Specify the substring to be replaced. All occurrences of from_substring found within string will be replaced with to_substring.
`to_substring`	`Required.` Specify the replacement substring. All occurrences of from_substring found within string will be replaced with to_substring.

Return Value

Returns the string with the replaced values.

Example 1:

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

mysql> SELECT REPLACE('SQL Tutorial', 'SQL', 'HTML');
Result: 'HTML Tutorial'

mysql> SELECT REPLACE('SQL Tutorial', 'sql', 'HTML');
Result: 'SQL Tutorial'

mysql> SELECT REPLACE('xyz xyz', 'xyz', 'abc');
Result: 'abc abc'

mysql> SELECT REPLACE('xyz xyz', 'XYZ', 'abc');
Result: 'xyz xyz'

mysql> SELECT REPLACE('100', '0', '5');
Result: '155'

mysql> SELECT REPLACE(100, 0, 5);
Result: '155'

Example 2:

Consider a database table called Employee with the following records:

EmpID	Name	City
AXZ1	John	London
AXZ2	Marry	New York
AXZ3	Jo	Paris
AXZ4	Kim	Amsterdam
AXZ5	Ramesh	New Delhi
AXZ6	Huang	Beijing

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 *, REPLACE(EmpID, 'AXZ', 'XAY') AS NewEmpID FROM Employee;

This will produce the result as shown below:

EmpID	Name	City	NewEmpID
AXZ1	John	London	XAY1
AXZ2	Marry	New York	XAY2
AXZ3	Jo	Paris	XAY3
AXZ4	Kim	Amsterdam	XAY4
AXZ5	Ramesh	New Delhi	XAY5
AXZ6	Huang	Beijing	XAY6

❮ MySQL Functions