Replace Text in a String in MySQL

In MySQL, the REPLACE() function allows you to substitute parts of a string with different text. This function is useful for correcting spelling, changing outdated terms, or formatting text across multiple records.

Replacing text in strings is used when working with data, especially for data cleanup, formatting, or updating specific values.

Using REPLACE() to Substitute Text in MySQL

The REPLACE() function in MySQL takes three arguments:

  • Original String: The string in which the replacement will occur.
  • Substring to Replace: The substring you want to replace.
  • Replacement Text: The new substring to insert in place of the old substring.

The basic syntax is:

</>
Copy
REPLACE(original_string, substring_to_replace, replacement_text)

This function returns the modified string with all instances of the substring replaced.

Setup for Examples: Creating a Sample Table

We’ll use a products table with product_name and description fields to demonstrate text replacement.

1. Create a new database called store_db:

</>
Copy
CREATE DATABASE store_db;

2. Select the store_db database:

</>
Copy
USE store_db;

3. Create the products table with product_name and description fields:

</>
Copy
CREATE TABLE products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(50),
    description VARCHAR(255)
);

4. Insert sample data into the products table:

</>
Copy
INSERT INTO products (product_name, description)
VALUES 
('Laptop', 'An excellent choice for work and gaming.'),
('Smartphone', 'A smartphone with great battery and camera quality.'),
('Headphones', 'Premium sound quality and noise-cancellation feature.');

With this setup complete, we can use the REPLACE() function in various examples.

Replace Text in a String in MySQL - Database setup for examples

Examples: Replacing Text in MySQL

Here are examples demonstrating how to replace text in strings using the REPLACE() function.

Replace Text in a String in MySQL - products table

1. Replacing a Word in the Description

To replace the word “excellent” with “ideal” in the description column for all products:

</>
Copy
SELECT product_name, REPLACE(description, 'excellent', 'ideal') AS updated_description
FROM products;

This query returns the description field with the word “excellent” replaced by “ideal” for each product.

Replace Text in a String in MySQL - Replacing a Word in the Description column

2. Replacing Spaces with Hyphens in Product Names

To format product_name values by replacing spaces with hyphens:

</>
Copy
SELECT product_name, REPLACE(product_name, ' ', '-') AS url_friendly_name
FROM products;

This query replaces spaces with hyphens in each product_name, making them suitable for URLs or filenames.

3. Removing Unwanted Characters

To remove any occurrences of “and” from the description field:

</>
Copy
SELECT product_name, REPLACE(description, 'and', '') AS clean_description
FROM products;

This query removes all instances of “and” from each product description, helpful for text cleanup.

Removing Unwanted Characters by using REPLACE() in MySQL

4. Using REPLACE() in an UPDATE Statement

To permanently replace the word “Premium” with “High-Quality” in the description field across the table:

</>
Copy
UPDATE products
SET description = REPLACE(description, 'Premium', 'High-Quality');

This query updates the description in the table, replacing “Premium” with “High-Quality” in each row.

Using REPLACE() in an UPDATE Statement in MySQL

Conclusion

The REPLACE() function in MySQL is a powerful tool for modifying string data, allowing you to substitute text, remove unwanted characters, or standardize field values. By using REPLACE() with SELECT or UPDATE statements, you can perform these operations across multiple rows efficiently.