skip to content

In PostgreSQL, why does regexp_replace(phone, '[^0-9]', '') leave most punctuation in '+1 (555) 010-2030', and how do you normalise it?

level: middleimportance: should knowfreq 42%

answer

  1. first match only by default
  2. a flag for every match
  3. N zero means all
  4. shorthand for non-digit
  5. validate after cleaning

basics

~20 s

regexp_replace replaces only the first match unless given the g flag, so it removed just the leading plus sign; regexp_replace(phone, '\D', '', 'g') strips every non-digit, and an anchored ~ check then validates the result.

solid answer

~40 s

PostgreSQL's `regexp_replace(string, pattern, replacement [, flags])` replaces **only the first** match by default, so `'[^0-9]'` removed the `+` and stopped, returning `'1 (555) 010-2030'`. Add the `g` flag, `regexp_replace(phone, '\D', '', 'g')`, or use the positional form with `N` set to 0, `regexp_replace(phone, '\D', '', 1, 0)`, to replace every match and get `'15550102030'`. `\D` is the non-digit shorthand; with `standard_conforming_strings` on, the default, a single backslash in an ordinary literal reaches the regex unchanged. Then validate with an anchored match such as `~ '^[0-9]{10,15}$'` or `regexp_like`, and use `\1`-style back-references in the replacement to format. Because patterns can be expensive, never run user-supplied regexes without a time limit.

code

sql · 6 lines
sql
SELECT phone,
       regexp_replace(phone, '\D', '')          AS first_only,
       regexp_replace(phone, '\D', '', 'g')     AS all_flag,
       regexp_replace(phone, '\D', '', 1, 0)    AS all_n_zero
FROM alert_subscribers
WHERE regexp_replace(phone, '\D', '', 'g') !~ '^[0-9]{10,15}$';

go deeper

for a junior

Remember that regexp_replace changes only the first match unless you pass the g flag, and that \D means any non-digit.

for a middle

Explain both signatures, including start and N with N = 0 meaning all, back-references in the replacement, and why validation must be anchored.

for a senior

Show you would clean on write, validate with anchored patterns, keep expensive regexes out of hot WHERE clauses, and cap user-supplied patterns with a timeout.

for a principal

Decide where normalisation lives: in the database for one consistent rule across services, or at the edge where country-specific phone libraries know more than a regex.

## Why one character disappeared In the stock-ticker application, price alerts go out by SMS, and subscribers type phone numbers in every format: `+1 (555) 010-2030`, `555.010.2030`, `555 010 2030`. A first attempt at cleaning: ```sql SELECT regexp_replace('+1 (555) 010-2030', '[^0-9]', ''); -- 1 (555) 010-2030 ``` `regexp_replace` searches from the start of the string and, **by default, replaces only the first match**. The first non-digit is the `+`, so only it is removed. The bug is easy to miss in testing: an input such as `5550102030` has no non-digits, and `555-0102` has only one, so both come out right. Many programming-language libraries replace every match by default, which trains the opposite expectation. ## Replacing every match The function has two shapes, and both can replace all matches: | Call | Effect | |---|---| | `regexp_replace(s, p, r)` | First match only | | `regexp_replace(s, p, r, 'g')` | Every match (flag `g`) | | `regexp_replace(s, p, r, 'gi')` | Every match, case-insensitive | | `regexp_replace(s, p, r, start)` | First match at or after character `start` | | `regexp_replace(s, p, r, start, N)` | The N-th match; `N = 0` replaces all | | `regexp_replace(s, p, r, start, N, flags)` | As above with flags; `g` is ignored when `N` is given | Key details: - If nothing matches, the input is returned unchanged; a NULL input gives NULL. - The replacement may contain `\1` to `\9` for parenthesized subexpressions and `\&` for the whole match. - Named arguments work too: `regexp_replace(string => phone, pattern => '\D', replacement => '', flags => 'g')`. ## A normalisation recipe 1. **Strip** every non-digit: `regexp_replace(phone, '\D', '', 'g')`. 2. **Validate** the digits with an anchored pattern: `cleaned ~ '^[0-9]{10,15}$'`, or `regexp_like(cleaned, '^[0-9]{10,15}$')`. Without `^` and `$`, `~` would accept any string that merely contains ten digits. 3. **Format** for display with back-references when the length is known: ```sql SELECT regexp_replace('5550102030', '^(\d{3})(\d{3})(\d{4})$', '(\1) \2-\3'); -- (555) 010-2030 ``` ## Other regex functions worth knowing - `regexp_count(s, p)` counts matches, handy for "has at least ten digits". - `regexp_substr(s, p, start, N, flags, subexpr)` returns the N-th match or one group of it. - `regexp_match(s, p)` returns the first match's groups as a `text[]`; `regexp_matches(s, p, 'g')` returns one row per match, so it is a set-returning function. - `regexp_split_to_table(s, p)` splits a string into rows on a regex delimiter. ## Escaping backslashes PostgreSQL's `standard_conforming_strings` setting is **on by default**, so an ordinary literal `'\D'` passes the backslash through to the regex engine. In an escape-string literal (`E'...'`) or with the setting off, you must double it: `E'\\D'`. Mixing the two styles in one codebase is a frequent source of patterns that silently match the letter `D` instead of non-digits. ## Safety and performance - The manual warns that regular expressions can be **contrived to take arbitrary time and memory**. If a search box lets users supply a pattern, escape it or treat it as a literal, and cap the statement with a timeout. - `regexp_replace` in a `WHERE` clause cannot use an ordinary index on `phone`. If you search by the normalised number often, store the normalised value or index the expression; both are other topics. - Clean data on the way in where possible, so every reader does not repeat the work. ## Checking a cleanup before running it Before an `UPDATE` rewrites every subscriber's phone, preview the effect: 1. Select the old value, the cleaned value and whether the cleaned value passes the anchored check. 2. Count the rows that fail validation and inspect a sample by hand; they are usually extensions, letters or numbers missing a country code. 3. Run the `UPDATE` only for rows that pass, and keep the failures for follow-up rather than storing an invalid number. This keeps a regex mistake from silently corrupting a whole column. The interview answer names the first-match default and the `g` flag, and then shows that you validate the result rather than trusting the replacement.

  • What does regexp_replace(phone, '\D', '', 1, 2) do?
    It starts searching at character 1 and replaces only the **second** non-digit match. With `N` greater than zero, exactly that occurrence is replaced; `N = 0` replaces all matches from `start` onwards, and a `g` flag is ignored whenever `N` is given.
  • Why might '\D' match the letter D in one environment but non-digits in another?
    With `standard_conforming_strings` on, the default, `'\D'` keeps its backslash and the regex sees `\D`. If the setting is off, or the literal is written as `E'\D'`, the string parser consumes the backslash and the regex receives just `D`. Writing `E'\\D'` in escape strings avoids it.

  • regexp_replace replaces every match unless you limit it.
  • The g flag can be combined with N to replace every match after the N-th.
  • An unanchored ~ '[0-9]{10}' proves the whole value is a ten-digit number.
  • User-supplied regex patterns are safe because ~ cannot run SQL.
  • In PostgreSQL a backslash in an ordinary string literal must always be doubled.