How to Replace Part of a String in MySQL? - Coding

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:

Greeting
Hello GeeksforGeeks!

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:

empId	name	email
1	Clark	[email protected]
2	Dave	[email protected]
3	Ava	[email protected]

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:

empId	name	email
1	Clark	[email protected]
2	Dave	[email protected]
3	Ava	[email protected]

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:

productId	productName	description
1	Laptop	High performance laptop with 16Gigabytes RAM and 512Gigabytes SSD
2	Smartphone	Latest model smartphone with 5G and 128Gigabytes storage
3	Tablet	Lightweight tablet with 10-inch display and 64Gigabytes storage

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.

FAQs on How to Replace Part of a String in MySQL

What is the REPLACE function in MySQL?

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.

How does the REPLACE function syntax work?

The syntax for the REPLACE function is as follows:
REPLACE(string, old_substring, new_substring)

Is the REPLACE function suitable for bulk updates?

Yes, the REPLACE function combined with the UPDATE statement allows for bulk updates in MySQL. It efficiently replaces occurrences of a substring within specified columns of a table.

How does the REPLACE function handle NULL values?

If the string or old_substring parameters in the REPLACE function are NULL, the function returns NULL.

Reffered: https://www.geeksforgeeks.org

Databases

Related
How to Check MySQL Database
How to Use Full Outer Join in MySQL (With Examples)
MySQL FOREIGN KEY Constraint
Nested Select Statement in MySQL
SQL INSERT INTO SELECT Statement

Type:	Geek
Category:	Coding
Sub Category:	Tutorial
Uploaded by:	Admin
Views:	13