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.

MySQL REPLACE String Function

The REPLACE function in MySQL is used to replace all the occurrences of a substring with some other string.

Note: MySQL REPLACE function performs case-sensitive replacements

REPLACE Syntax

REPLACE(string, old_substring, new_substring)

Parameters Values

• string: The string in which to replace.
• old_substring: The substring to be replaced.
• new_substring: The substring to replace the old substring.

REPLACE() Function Example

The following query replaces “World” with “GeeksforGeeks“

SELECT REPLACE("Hello World!", "World", "GeeksforGeeks") AS Greeting;

Output:

Replaced output

Explanation:

The given SQL query utilizes the REPLACE function to modify the string “Hello World!” by replacing every occurrence of the substring “World” with “GeeksforGeeks“. The resulting output assigned the alias “Greeting“, is “Hello GeeksforGeeks!” – a string where the specified substitution has been applied, effectively altering the original greeting.

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:

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.

Conclusion

REPLACE function is used to replace a part of string in MySQL. It is one of the most important functions for string manipulation in MySQL.

Using REPLACE function with UPDATE statement allows to replace a part of a string in a table column. This is very useful to replace a specific part of string throughout the column as we saw in the examples.