MySQL - REPLACE() Function

---

---

The MySQL REPLACE() function is used to replace all occurrences of a specific substring in a given string with another specified substring.

This function accepts three parameters: the original string, the substring to be replaced, and the substring to replace it with. It can be used in a variety of scenarios such as replacing a particular word in a sentence or updating values in a database.

Syntax

Following is the syntax of MySQL REPLACE() function −

REPLACE(str,from_str,to_str)

Parameters

This function takes a string, a search string, and a replacement string as parameter.

Return Value

This function returns a new string with all occurrences of the search string replaced by the replacement string.

Example

In the following example, we are replacing the occurrence of 'Hello' with 'Hi' in the given string −

SELECT REPLACE('Hello how are you', 'Hello', 'Hi');

Following is the output of the above code −

REPLACE('Hello how are you', 'Hello', 'Hi')
Hi how are you

Example

If any of the arguments passed to the function is NULL, it returns NULL −

SELECT REPLACE('Hello how are you', 'Hello', NULL);

The output obtained is as follows −

REPLACE('Hello how are you', 'Hello', NULL)
NULL

Example

You can also pass numerical values as the first argument to this function −

SELECT REPLACE(4125412387981236985123, 123, 000);

We get the output as follows −

REPLACE(4125412387981236985123, 123, 000)
4125408798069850

Example

While replacing this function performs a case-sensitive match. If you try to replace the substring by changing the case, this function generates an error −

SELECT REPLACE('Hello how are you', HOW, 'Hi');

Following is the output of the above code −

ERROR 1054 (42S22): Unknown column 'HOW' in 'field list'

Example

You can also pass column name of a table as an argument to this function and replace the part of the values in it.

Let us create a table named "EMP" and insert records into it using CREATE and INSERT statements as shown below −

CREATE TABLE EMP(
   FIRST_NAME  CHAR(20) NOT NULL,
   LAST_NAME  CHAR(20),
   AGE INT,
   INCOME FLOAT
);

Now, let us insert records into it using the INSERT statement −

INSERT INTO EMP VALUES 
('Krishna', 'Sharma', 19, 2000),
('Raj', 'Kandukuri', 20, 7000),
('Ramya', 'Ramapriya', 25, 5000),
('Mac', 'Mohan', 26, 2000);

The EMP obtained is as follows −

FIRST_NAME	LAST_NAME	AGE	INCOME
Krishna	Sharma	19	2000
Raj	Kandukuri	20	7000
Ramya	Ramapriya	25	5000
Mac	Mohan	26	2000

Following query replaces the string "Krishna" in the entities of the column "FIRST_NAME" with '$$$$$$$' using the REPLACE() function −

SELECT FIRST_NAME, LAST_NAME, AGE, REPLACE(FIRST_NAME, 'Krishna', '$$$$$$$') as Result 
FROM EMP;

Output

After executing the above code, we get the following output −

FIRST_NAME	LAST_NAME	AGE	Result
Krishna	Sharma	19	$$$$$$$ Sharma
Raj	Kandukuri	20	Raj Kandukuri
Ramya	Ramapriya	25	Ramya Ramapriya
Mac	Mohan	26	Mac Mohan