In PostgreSQL, why does regexp_replace(phone, '[^0-9]', '') leave most punctuation in '+1 (555) 010-2030', and how do you normalise it?
answer
- first match only by default
- a flag for every match
- N zero means all
- shorthand for non-digit
- validate after cleaning
basics
~20 sregexp_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 sPostgreSQL'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 linesSELECT 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
Remember that regexp_replace changes only the first match unless you pass the g flag, and that \D means any non-digit.
Explain both signatures, including start and N with N = 0 meaning all, back-references in the replacement, and why validation must be anchored.
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.
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.