MySQL Remove String from Column

Two functions handle mysql remove string from column: REPLACE for literal substrings, REGEXP_REPLACE (MySQL 8+) for patterns. Use REPLACE when the target is a fixed string — a deprecated domain, a tracking prefix, a stale watermark. Use REGEXP_REPLACE when the target varies — any digit, any non-alphanumeric, any character outside an allow-list. This guide covers both and the preview-then-update workflow that keeps mass updates safe.

Contents

TL;DR
Literal strings with REPLACE
Patterns with REGEXP_REPLACE
Preview before UPDATE
Case sensitivity
Back up first
Frequently asked questions
Related guides
References

Last verified: 2026-04-23 on MySQL 8.0 and MariaDB 10.11. Originally published 2022-12-20, rewritten and updated 2026-04-23.

TL;DR

-- Literal string: remove "http://old-domain.com/" wherever it appears
UPDATE table_name
SET column_name = REPLACE(column_name, 'http://old-domain.com/', '')
WHERE column_name LIKE '%http://old-domain.com/%';

-- Pattern: strip every non-alphanumeric character
UPDATE table_name
SET column_name = REGEXP_REPLACE(column_name, '[^A-Za-z0-9]', '');

Literal strings with `REPLACE`

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

REPLACE(str, search, replace) replaces every occurrence of search with replace. Passing '' as the replacement deletes. Works on any MySQL/MariaDB version — no regex engine, no version gate.

The WHERE column_name LIKE '%substring_to_remove%' clause is optional but wise: it limits the UPDATE to rows that actually contain the target, reducing the number of modified rows and the risk of an accidental rewrite.

mysql remove string from column — REPLACE for literal strings, REGEXP_REPLACE for patterns

Patterns with `REGEXP_REPLACE`

UPDATE table_name
SET column_name = REGEXP_REPLACE(column_name, 'pattern_to_match', '')
WHERE condition;

Common patterns:

[a-z] — any lowercase letter.
[0-9] — any digit.
[A-Za-z0-9] — any alphanumeric character.
[^A-Za-z0-9] — any non-alphanumeric character.
\\d+ — one or more consecutive digits.
\\w+ — one or more consecutive word characters (letters, digits, underscore).
\\s+ — one or more whitespace characters.

Combine with quantifiers (+, *, {n,m}) for length-bounded matches. See extracting only digits for the inverse “keep instead of remove” approach.

Preview before UPDATE

-- See what the UPDATE would do, without writing
SELECT
    id,
    column_name                              AS before_value,
    REPLACE(column_name, 'target', '')       AS after_value
FROM table_name
WHERE column_name LIKE '%target%'
LIMIT 50;

Always check the transform against 50–100 rows before running on the whole table. It’s the cheapest way to catch a pattern mistake (e.g. forgetting a trailing slash in a URL would match and rewrite more than you intended).

Case sensitivity

-- Column's default collation: matches "Target", "TARGET", "target"
UPDATE t SET col = REPLACE(col, 'Target', '');

-- Force byte-exact matching (case-sensitive)
UPDATE t SET col = REPLACE(BINARY col, 'Target', '');

MySQL’s default utf8mb4_unicode_ci collation is case-insensitive, so REPLACE treats "Target" and "target" as the same string. Wrap the column in BINARY to get byte-level matching when you need the exact casing.

Back up first

# Command-line backup before a mass UPDATE
mysqldump -u root -p mydb table_name > table_backup.sql

A mass UPDATE with REPLACE or REGEXP_REPLACE can’t be undone without a backup. For table-level snapshots, mysqldump is the standard — see exporting and importing MySQL databases for the full-database version.

Frequently asked questions

What’s the simplest mysql remove string from column query?

UPDATE table SET col = REPLACE(col, 'to_remove', ''). REPLACE swaps every occurrence of the literal target string with the replacement — pass an empty replacement to delete. Works on any MySQL/MariaDB version; no regex engine needed.

When should I use REGEXP_REPLACE instead?

When the string to remove isn’t a fixed literal — you want to strip all digits, any non-alphanumeric character, or anything matching a pattern. REGEXP_REPLACE takes a regex as the target; REPLACE takes a literal string. REGEXP_REPLACE requires MySQL 8.0+ or MariaDB 10.0.5+.

Is the REPLACE function case-sensitive?

Depends on the column’s collation. Columns with a _ci collation (case-insensitive — the default for utf8mb4_unicode_ci) match case-insensitively; _cs or _bin collations are case-sensitive. For explicit control, wrap in BINARY to force a byte-level comparison: REPLACE(BINARY col, 'Target', '') only matches the exact casing.

Can I preview the changes before committing?

Yes — run the equivalent SELECT first: SELECT id, column_name, REPLACE(column_name, 'to_remove', '') AS preview FROM table_name WHERE column_name LIKE '%to_remove%'. Inspect the output, then run the UPDATE. Always back up the table before a mass string update — REPLACE is destructive.

Why does my regex not match special characters?

MySQL’s REGEXP_REPLACE uses POSIX-style regex with a couple of MySQL-specific rules: double-escape literal backslashes ('\\\\.' to match a literal dot), and use single-quoted strings with care because MySQL’s own string escaping runs first. For complex patterns test them in a minimal SELECT before wrapping in UPDATE — a typo in the pattern that matches nothing is better than one that matches too much.