Common mistake

SQL: Why regexp_replace Only Replaces the First Match

In DuckDB and PostgreSQL, regexp_replace changes the first match and leaves the rest. Pass 'g' to replace every match, or use replace() when the text to remove isn't a pattern.

The symptom

Garden Shop stores phone numbers as 215-555-0142. You strip the dashes with regexp_replace(phone, '-', '') to get ten digits, and every number comes back as 215555-0142, with one dash still in it. A check for ten-character results finds 0 of the 15 phone numbers.

Why it happens

In DuckDB and PostgreSQL, regexp_replace replaces the first match and stops. Replacing every match is an option you have to ask for. Nothing errors, and the result looks almost right, which is why it slips through: the first dash is gone.

The fix

Pass 'g' (global) as the fourth argument, and every match is replaced:

Wrong: only the first dash goes

select customer_id,
       phone,
       regexp_replace(phone, '-', '') as digits
from customers
where phone is not null
order by customer_id
limit 5

Right: 'g' replaces every match

select customer_id,
       phone,
       regexp_replace(phone, '-', '', 'g') as digits
from customers
where phone is not null
order by customer_id
limit 5

With 'g', all 15 numbers come out as ten digits. The option combines with others, so 'gi' replaces every match and ignores case.

Or skip the regular expression

A dash isn't a pattern, it's a character. replace treats its arguments as plain text and always replaces every occurrence, in every engine:

Plain text, every occurrence

select count(*) filter (where length(regexp_replace(phone, '-', '')) = 10)      as first_only,
       count(*) filter (where length(regexp_replace(phone, '-', '', 'g')) = 10) as global,
       count(*) filter (where length(replace(phone, '-', '')) = 10)             as plain_replace
from customers

That returns 0, 15, and 15. Keep regexp_replace for real patterns, such as '[^0-9]' with 'g' to drop anything that isn't a digit, whatever the separator was.

Dialect note

The default differs by engine. MySQL, Oracle, and Snowflake replace every match unless told otherwise, so a query written there and moved to DuckDB or PostgreSQL quietly starts replacing only the first. Writing 'g' in DuckDB, or using replace for plain text, makes the intent explicit.