SysTutorials

Updating String Values in MySQL with REPLACE()

· Eric Ma · updated April 12, 2026

The REPLACE() function is your primary tool for modifying string values within a column. It scans for a substring and replaces all occurrences with a new value.

Basic syntax

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

Simple example

Replace all instances of “old_domain” with “new_domain” in the email column:

UPDATE users
SET email = REPLACE(email, 'old_domain.com', 'new_domain.com');

This updates every row. If you need to target specific records, add a WHERE clause:

UPDATE users
SET email = REPLACE(email, 'old_domain.com', 'new_domain.com')
WHERE email LIKE '%old_domain.com%';

Case-sensitive replacements

By default, REPLACE() is case-insensitive in MySQL. If you need case-sensitive matching, use BINARY:

UPDATE users
SET email = REPLACE(BINARY email, 'OldValue', 'NewValue');

Multiple replacements in one query

Chain multiple REPLACE() calls:

UPDATE products
SET description = REPLACE(
  REPLACE(description, 'old_color', 'new_color'),
  'old_size', 'new_size'
);

Using REGEXP for pattern-based replacement

For more complex patterns, use REGEXP_REPLACE() (available in MySQL 8.0+):

UPDATE logs
SET message = REGEXP_REPLACE(message, '[0-9]{3}-[0-9]{4}', 'XXX-XXXX');

This masks phone numbers in a message column.

Preview changes before updating

Always preview the changes first. Use a SELECT statement with the replacement logic:

SELECT id, email, REPLACE(email, 'old_domain.com', 'new_domain.com') AS new_email
FROM users
WHERE email LIKE '%old_domain.com%';

Review the output before running the UPDATE.

Performance considerations

On large tables, updates can lock resources. Consider:

UPDATE users
SET email = REPLACE(email, 'old_domain.com', 'new_domain.com')
WHERE email LIKE '%old_domain.com%'
LIMIT 1000;

Then repeat until all rows are updated.

Replacing with NULL

To remove a substring by replacing it with nothing:

UPDATE articles
SET title = REPLACE(title, 'deprecated_keyword', '');

To replace with NULL:

UPDATE articles
SET title = REPLACE(title, 'deprecated_keyword', NULL);

Working with multiple columns

Update several columns at once:

UPDATE employees
SET first_name = REPLACE(first_name, 'John', 'Jon'),
    last_name = REPLACE(last_name, 'Smith', 'Smythe')
WHERE department_id = 5;

Safe practices

  1. Back up before bulk updates — Always maintain a recent database backup.
  2. Test on a development instance — Verify the replacement logic doesn’t produce unexpected results.
  3. Use transactions for safety — Wrap large updates in a transaction so you can roll back if needed:
START TRANSACTION;
UPDATE users
SET email = REPLACE(email, 'old_domain.com', 'new_domain.com')
WHERE email LIKE '%old_domain.com%';
-- Review the result, then:
COMMIT;
-- Or ROLLBACK; if something looks wrong
  1. Log your changes — Document what was replaced, when, and why for audit trails.
  2. Verify after the update — Run a SELECT query to confirm the replacements worked correctly:
SELECT COUNT(*)
FROM users
WHERE email LIKE '%old_domain.com%';

This count should be zero after a successful replacement.

2026 Best Practices and Advanced Techniques

For Updating String Values in MySQL with REPLACE(), understanding both the fundamentals and modern practices ensures you can work efficiently and avoid common pitfalls. This guide extends the core article with practical advice for 2026 workflows.

Troubleshooting and Debugging

When issues arise, a systematic approach saves time. Start by checking logs for error messages or warnings. Test individual components in isolation before integrating them. Use verbose modes and debug flags to gather more information when standard output is not enough to diagnose the problem.

Performance Optimization

Security Considerations

Security should be built into workflows from the start. Use strong authentication methods, encrypt sensitive data in transit, and follow the principle of least privilege for access controls. Regular security audits and penetration testing help maintain system integrity.

These complementary tools expand your capabilities:

Integration with Modern Workflows

Consider automation and containerization for consistency across environments. Infrastructure as code tools enable reproducible deployments. CI/CD pipelines automate testing and deployment, reducing human error and speeding up delivery cycles.

Quick Reference

This extended guide covers the topic beyond the original article scope. For specialized needs, refer to official documentation or community resources. Practice in test environments before production deployment.

← All posts