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 occurrences of a specified substring within a string with another substring. It is case-sensitive, meaning it distinguishes between uppercase and lowercase characters during replacement.

Note: MySQL REPLACE function performs case-sensitive replacements

Syntax:

REPLACE(string, old_substring, new_substring)

Parameters:

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

Simple String Replacement

The following query replaces "World" with "GeeksforGeeks"

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

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 the 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', '[email protected]');
   (0002, 'Dave', '[email protected]');
   (0003, 'Ava', '[email protected]');

Output:

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

Example of Using 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 [email protected] to [email protected].

Example of Updating Product Descriptions

We will create a PRODUCTS table with fields like productId, productName, and description.

CREATE TABLE PRODUCTS (
  productId INTEGER PRIMARY KEY,
  productName TEXT NOT NULL,
  description TEXT NOT NULL
);

INSERT INTO PRODUCTS (productId, productName, description) VALUES 
   (1, 'Laptop', 'High performance laptop with 16GB RAM and 512GB SSD'),
   (2, 'Smartphone', 'Latest model smartphone with 5G and 128GB storage'),
   (3, 'Tablet', 'Lightweight tablet with 10-inch display and 64GB storage');

Query:

UPDATE PRODUCTS SET description = REPLACE(description, 'GB', 'Gigabytes');

Output:

Conclusion

The REPLACE function is a powerful tool for string manipulation in MySQL. It allows you to replace parts of strings within a table column efficiently. Using the REPLACE function with the UPDATE statement enables you to perform bulk updates on string data, making it a crucial function for database administrators and developers. By mastering this function, you can handle various string manipulation tasks, from simple replacements to complex modifications, with ease.