MySQL Replace Part of String in Column Example
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:
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:
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:
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.
Contact Us