What is MySQL Replace Part of String in Column Example?

In this article, we will learn MySQL Replace Part of String in Column Example,This free MySQL tutorial for complete beginners will help you learn MySQL from scratch.

MySQL Replace Part of String in Column Example

REPLACE() Function Example

Conclusion

We have covered how to use REPLACE function to change the part of a string in MySQL. Now let’s see how to do the same, when the string is in a column of MySQL table.

Let’s start by creating a table and adding some sample data to the table. We create an EMPLOYEE table which contains fields like empId, name, and the email of the person. The following query creates the table:

MySQL

CREATE TABLE EMPLOYEE (
  empId INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT NOT NULL
);

INSERT INTO EMPLOYEE VALUES 
   (0001, 'Clark', 'clark@some.com');
   (0002, 'Dave', 'dave@some.com');
   (0003, 'Ava', 'ava@some.com');

Output:

Initial data

To replace a part of string in column, we will use REPLACE function with UPDATE clause.

Replace ‘some.com’ to ‘domain.net’ in email column of EMPLOYEE table

UPDATE EMPLOYEE SET email=REPLACE(email, 'some.com', 'domain.net');

The following is the data of the table after executing the above query:

Output:

Updated data

Explanation:

The email domain for each employee has been updated from ‘some.com‘ to ‘domain.net’. The REPLACE function ensures that occurrences of ‘some.com’ in the email column are replaced with ‘domain.net’.

As you can see the email of each employee has changed from user@some.com to user@domain.net.

How to Replace Part of a String in MySQL?

To replace a part of a string in MySQL we use the REPLACE function. MySQL provides this method to manipulate string data in the tables.

In this article, we are going to see how we can update a part of the string with some other value in MySQL. Understanding this can enable string manipulation in a much more complex fashion.