Write SQL query to replace specific patterns in a string column.

Use REPLACE for literal substrings and a regex function when the pattern varies.

-- Literal: strip dashes from phone numbers
SELECT REPLACE(phone, '-', '') AS phone_clean
FROM   customers;
-- Regex: collapse any run of whitespace to a single space
SELECT REGEXP_REPLACE(address, '\s+', ' ', 'g') AS address_clean
FROM   customers;

Dialect differences matter here

REPLACE is portable and behaves the same nearly everywhere. The regex function is not:

Naming the dialect you are targeting is worth a point on its own.

NULL handling

Both functions return NULL when the input is NULL rather than an empty string. If the column is nullable and downstream logic expects text, wrap it: COALESCE(REPLACE(col, 'x', 'y'), ''). Forgetting this is how NULLs leak into a supposedly cleaned column.

Replacing several patterns

REPLACE calls nest, and they evaluate inside out:

SELECT REPLACE(REPLACE(REPLACE(phone, '-', ''), '(', ''), ')', '')
FROM   customers;

Order matters if one replacement can create text that a later replacement then matches. When you are only deleting or swapping individual characters, TRANSLATE does it in a single pass and is much easier to read.

Performance

Neither function is sargable. Wrapping a column in REPLACE inside a WHERE clause disables any index on that column and forces a full scan. If you filter on the cleaned value regularly, store it as a generated or materialised column and index that instead of cleaning on every read. Regex is also substantially more expensive than a literal replace, so do not reach for it when REPLACE suffices.

In the interview, also mention that repeated cleaning at query time is a signal the transformation belongs upstream in the pipeline.