How to Replace Text in MySQL Table Column

Imagine that you have a MySQL table and now need to update the table by replacing some particular strings in some columns. Updating every row one by one is not a solution at all when you have thousands of rows. So this is where we can use the MySQL REPLACE() function. In order to demonstrate, first, create the following sample table.

CREATE TABLE `customers` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `customer_id` varchar(16) NOT NULL,
    `name` varchar(64) NOT NULL,
    `phone` varchar(20) NOT NULL,
    `address` varchar(128) NOT NULL,
    PRIMARY KEY (`id`)
);

Then insert some random data into it.

INSERT INTO `customers` (`id`, `customer_id`, `name`, `phone`, `address`) VALUES
(1, 'cstid17281', 'Elaina Winklevoss', '(624) 299-3051', '1682\nMayert Underpass'),
(2, 'cstid17282', 'Sibyl Tracy', '(713) 315-6595', '8154\nChristian Lodge'),
(3, 'cstid17283', 'Luis Myrtis', '1-469-986-0362', '31725\r\nBradford Cliffs Suite'),
(4, 'cstid17284', 'Marvin Madilyn', '558-498-7673', '6060\r\nFermin Motorway'),
(5, 'cstid17285', 'Harvey Webster', '947-224-6119', '5138\r\nRod Bypass Suite');

Replace A Particular String

Replace customer ids by replacing the “cstid” part of them with “cid_”.

UPDATE customers SET customer_id = REPLACE(customer_id, 'cstid', 'cid_');

Replace A Particular Set Of Characters

Remove brackets, dashes and spaces from the customer phone numbers column. For that, we can run a separate query for each character like this.

UPDATE customers SET phone = REPLACE(phone, ' ', '');

Or we can even combine all of them into one query like this.

UPDATE customers SET phone =
REPLACE(
    REPLACE(
        REPLACE(
            REPLACE(phone, ' ', ''),
        '(', ''),
    ')', ''),
'-', '');

Replace Newline Characters

In the address column, we need to replace newlines (\n) and carriage returns (\r) with spaces. Newline moves the cursor to the next line while the carriage return moves the cursor to the beginning of the current line. Once we replaced newlines and carriage returns with spaces, there can be repetitive spaces. On the last line, we search for double spaces and replace them with a space.

UPDATE customers SET address =
REPLACE(
    REPLACE(
        REPLACE(address, '\n', ' '),
    '\r', ' '),
'  ', ' ');

Likewise, you can replace tabs with \t as well.