MySQL Replace String in Column

Replace String in MySQL

To replace text stored in a MySQL column, use the REPLACE() string function inside an UPDATE statement. MySQL returns the original value with every exact occurrence of the search string replaced, and the UPDATE statement saves that result back to the column.

The usual workflow is to preview the affected rows with SELECT, run a targeted UPDATE, and then verify the changed values. This is especially useful for correcting URL prefixes, phone numbers, product codes, labels, and other repeated text.

MySQL UPDATE REPLACE Syntax for a Column

The syntax to replace a string in a column in SQL is

UPDATE table_name
SET column_name = REPLACE(column_name, 'old_string', 'new_string')
WHERE column_name LIKE ('%old_string%');

In this statement:

• table_name is the table that contains the values to change.
• column_name is the text column in which the replacement is performed.
• old_string is the exact text to find.
• new_string is the replacement text.
• The WHERE clause limits the update to matching rows. Without it, MySQL evaluates every row in the table.

REPLACE() replaces all occurrences of old_string within each selected value. Its search is case-sensitive, so replacing 'mysql' does not replace 'MySQL'. If any argument passed to REPLACE() is NULL, the function returns NULL.

Preview Rows Before Replacing Text in a MySQL Column

Before updating stored data, run a SELECT query with the same condition. You can also display the proposed value beside the current value.

SELECT
    sometext AS current_value,
    REPLACE(sometext, '9876543210', '0123456789') AS proposed_value
FROM sampletable
WHERE sometext LIKE '%9876543210%';

This preview helps confirm that the search string, replacement string, and row filter are correct before data is modified.

Example: Replace a String in the sometext Column

Consider the following sampletable MySQL Table.

Let us replace the string '9876543210' with '0123456789' in the sometext column.

The following query updates every row, applying REPLACE() to the value stored in sometext.

UPDATE sampletable
SET sometext = REPLACE(sometext, '9876543210', '0123456789')

In the shown result, four rows were examined and three rows were changed. A row can be matched or examined without being changed when the expression produces the same value that is already stored.

Limit MySQL String Replacement with a WHERE Clause

Add a WHERE clause when only rows containing the old string should be updated. This makes the intent clearer and avoids applying the assignment to unrelated rows.

UPDATE sampletable
SET sometext = REPLACE(sometext, '9876543210', '0123456789')
WHERE sometext LIKE ('%9876543210%');

Here, only the three rows containing the old string are matched and updated. The exact execution time depends on the table size, storage engine, indexes, server load, and the selectivity of the condition. A pattern beginning with %, such as LIKE '%text%', commonly requires MySQL to inspect values rather than use a normal index lookup.

Replace Text Only in Selected MySQL Rows

The text condition can be combined with a primary key, status, date, category, or another column so that the update affects only the intended records.

UPDATE customers
SET website = REPLACE(website, 'http://', 'https://')
WHERE customer_id BETWEEN 100 AND 200
  AND website LIKE 'http://%';

This query changes the URL prefix only for customers in the specified ID range whose website begins with http://.

Remove Characters or Substrings with MySQL REPLACE()

To remove text instead of substituting it, use an empty string as the third argument. The following example removes hyphens from phone numbers.

UPDATE contacts
SET phone = REPLACE(phone, '-', '')
WHERE phone LIKE '%-%';

For example, 987-654-3210 becomes 9876543210. The same method can remove spaces, punctuation, prefixes, or other exact character sequences.

Replace Multiple Strings in One MySQL UPDATE

You can nest REPLACE() calls when several exact substitutions must be applied to the same value.

UPDATE product_descriptions
SET description = REPLACE(
    REPLACE(description, '&', 'and'),
    '  ',
    ' '
)
WHERE description LIKE '%&%'
   OR description LIKE '%  %';

The inner replacement is evaluated first, and its result is passed to the outer replacement. For many complex substitutions, separate reviewed updates may be easier to test and maintain.

Use REGEXP_REPLACE() for Pattern-Based Replacement in MySQL 8

REPLACE() finds a literal string. When the text varies but follows a pattern, MySQL 8 provides REGEXP_REPLACE(). The following example replaces one or more whitespace characters with a single space.

UPDATE customer_notes
SET note_text = REGEXP_REPLACE(note_text, '[[:space:]]+', ' ')
WHERE REGEXP_LIKE(note_text, '[[:space:]]{2,}');

Regular expressions are useful for pattern-based cleanup, but the pattern should be tested with a SELECT query first. Use ordinary REPLACE() when the exact text is known.

Run a MySQL UPDATE REPLACE Safely

A string replacement can change many rows at once. For important data, use a backup and test the statement in a transaction when the table uses a transactional storage engine such as InnoDB.

START TRANSACTION;

UPDATE sampletable
SET sometext = REPLACE(sometext, '9876543210', '0123456789')
WHERE sometext LIKE '%9876543210%';

SELECT *
FROM sampletable
WHERE sometext LIKE '%0123456789%';

-- Keep the changes after verification.
COMMIT;

-- Use ROLLBACK instead of COMMIT to discard them.

Applications should use parameterized statements for user-supplied values instead of building SQL by concatenating untrusted input.

REPLACE() Function and REPLACE Statement Are Different

MySQL uses the word REPLACE for two different features. REPLACE(string, from_string, to_string) is a string function. The separate REPLACE INTO statement inserts a row or replaces an existing row when a primary-key or unique-key conflict occurs. Use the function inside UPDATE when changing part of a column value.

Common MySQL String Replacement Mistakes

Mistake	Result	Better approach
Omitting WHERE	Every row is evaluated and may be changed.	Preview and reuse a precise filter.
Assuming replacement is case-insensitive	Values with different letter case remain unchanged.	Match the exact case or use a reviewed case-normalization strategy.
Expecting only the first occurrence to change	REPLACE() changes every occurrence in the value.	Use another string expression when only one occurrence should change.
Confusing REPLACE() with REPLACE INTO	The operation may affect whole rows instead of text inside a value.	Use UPDATE ... SET column = REPLACE(...).
Skipping a preview or backup	An incorrect pattern can modify many records.	Run SELECT first and use a transaction or backup.

MySQL Replace String in Column FAQs

Does MySQL REPLACE() change every occurrence in a column value?

Yes. For each row selected by the UPDATE, REPLACE() substitutes every exact occurrence of the search string within that column value.

Is MySQL REPLACE() case-sensitive?

Yes. The search performed by REPLACE() is case-sensitive. A search for 'cat' does not replace 'Cat'.

How do I remove a character from every matching MySQL value?

Pass an empty string as the replacement value, for example REPLACE(phone, '-', ''), and restrict the update with an appropriate WHERE clause.

Can MySQL replace text using a regular expression?

Yes. MySQL 8 supports REGEXP_REPLACE() for pattern-based replacement. Use REPLACE() for exact literal text and REGEXP_REPLACE() when a regular-expression pattern is required.

MySQL String Replacement QA Checklist

• Confirm that the target table and column names are correct.
• Preview the same rows with SELECT before running UPDATE.
• Verify the exact letter case of the old string.
• Check whether all occurrences or only one occurrence should be replaced.
• Use a topic-specific WHERE condition to limit affected rows.
• Test nested or regular-expression replacements on sample values.
• Keep a backup or use a transaction before a large update.
• Verify both the number of affected rows and the resulting values.

Summary

Use UPDATE with the MySQL REPLACE() function to replace exact text inside a column. Preview the transformation, limit the affected rows with a suitable WHERE clause, and verify the result before committing large changes. For pattern-based replacement in MySQL 8, use REGEXP_REPLACE(). Continue with the MySQL Tutorial for related SQL examples.