How to Replace a String in MySQL

In this tutorial, we aim at exploring how to replace a string in MySQL.

In specific tables in MySQL, we periodically need to update certain string values to reflect the updated status or product list of a company in a particular table of a database. MySQL provides us with a REPLACE() function to help us achieve this task efficiently.

Let us understand more about this REPLACE() function.

The REPLACE() method in MySQL substitutes all incidences of a string value with a new string. This function takes three input parameters.

First is the column name from where we wish to find the string value. Second is the string value itself that needs to be replaced, and lastly, we pass the replacement string value.

The syntax of the REPLACE() function is as follows.

REPLACE(column_name, old_string_to_be_replaced, new_string_value)

Let us understand how this method works.

Before we begin, we create a dummy dataset to work on. We create a table, student_details, along with a few rows.

-- create the table student_details
CREATE TABLE student_details(
  stu_id int,
  stu_firstName varchar(255) DEFAULT NULL,
  stu_lastName varchar(255) DEFAULT NULL,
  primary key(stu_id)
);
-- insert rows to the table student_details
INSERT INTO student_details(stu_id,stu_firstName,stu_lastName) 
 VALUES(1,"Preet","Sanghavi"),
 (2,"Rich","John"),
 (3,"Veron","Brow"),
 (4,"Geo","Jos"),
 (5,"Hash","Shah"),
 (6,"Sachin","Parker"),
 (7,"David","Miller");

Replace String in MySQL

In the student_details table, let us try to replace the stu_firstName with the Preet to Preeti. We can perform this operation using the following query.

SELECT REPLACE(stu_firstName, 'Preet', 'Preeti') as new_firstNames from student_details;

The output of the query would be as follows.

new_firstNames
Preeti
Rich
Veron
Geo
Hash
Sachin
David

The REPLACE() function for this task is the UPDATE statement in MySQL that can help us alter the string in the table. To replace a string within the table, we can use the following query.

UPDATE name_of_the_table set column_name =REPLACE(column_name,'old_string','new_string');

To replace the Preet first name of the student in the student_details table, we can execute the following query to get the job done.

update student_details set stu_firstName=REPLACE(stu_firstName,'Preet','Preeti');

The output of the query above would give us the following result.

stu_id	stu_firstName	stu_lastName
1	      Preeti	    Sanghavi
2	      Rich	        John
3	      Veron	        Brow
4	      Geo	        Jos
5	      Hash	        Shah
6	      Sachin	    Parker
7	      David	        Miller

As the output indicates, the first name with string value Preet and the stu_id as 1 has been updated to Preeti.

Therefore, with the help of the REPLACE method, we can efficiently rename strings in a table in MySQL.