Replace Substrings With a Regular Expression

Use `REGEXP_REPLACE` to substitute every match of a pattern in a string with a replacement value.

3/3 supported engines validation-green 1 example 2 scenarios

Docker-validated Not currently validation-green

Example 1

Mask phone numbers in a message column

The pattern [0-9]{3}-[0-9]{3}-[0-9]{4} matches three digits, a hyphen, three digits, a hyphen, and four digits — the standard North American phone format. Both occurrences in the string are replaced with the literal mask. MySQL/MariaDB replace all matches automatically; PostgreSQL requires the 'g' (global) flag to replace all rather than just the first match.

MySQL MariaDB

Engine-specific syntax

Source table data

Setup

CREATE TABLE messages (id INT, body VARCHAR(200));

INSERT INTO
  messages
VALUES
  (1, 'Call 555-123-4567 or 555-987-6543 for info');

Validated query

SQL

SELECT
  id,
  REGEXP_REPLACE (
    body,
    '[0-9]{3}-[0-9]{3}-[0-9]{4}',
    'XXX-XXX-XXXX'
  ) AS masked
FROM
  messages
ORDER BY
  id;

Expected result

id	masked
1	Call XXX-XXX-XXXX or XXX-XXX-XXXX for info

PostgreSQL

Engine-specific syntax

Source table data

Setup

CREATE TABLE messages (id INT, body VARCHAR(200));

INSERT INTO
  messages
VALUES
  (1, 'Call 555-123-4567 or 555-987-6543 for info');

Validated query

SQL

SELECT
  id,
  REGEXP_REPLACE (
    body,
    '[0-9]{3}-[0-9]{3}-[0-9]{4}',
    'XXX-XXX-XXXX',
    'g'
  ) AS masked
FROM
  messages
ORDER BY
  id;

Expected result

id	masked
1	Call XXX-XXX-XXXX or XXX-XXX-XXXX for info

PostgreSQL requires the 'g' flag to replace all occurrences. MySQL and MariaDB replace all matches by default. SQL Server and SQLite do not support REGEXP_REPLACE.

Useful when

Where this command helps.

masking phone numbers, email addresses, or other PII before logging or exporting
normalising inconsistently formatted strings by collapsing whitespace or removing punctuation

Explanation

What the command is doing.

REGEXP_REPLACE(str, pattern, replacement) scans str for matches of pattern and replaces each one with replacement. MySQL (8.0+) and MariaDB (10.0.5+) support it with standard PCRE syntax, replacing all matches by default. PostgreSQL uses the same function name and replaces all matches when no flags argument is given. SQL Server has no built-in REGEXP_REPLACE — for literal substitutions use REPLACE; for pattern-based work a CLR function or iterative approach is needed. SQLite has no built-in regex functions unless the host application registers them.

Replace Substrings With a Regular Expression

Mask phone numbers in a message column

Where this command helps.

What the command is doing.

Keep moving through nearby tasks.