Replaces the parts of a string that match a regular expression.
REGEXP_REPLACE(<text>, '<pattern>', <to>)
REGEXP_REPLACE(<text>, '<pattern>', <to>, '<flags>')
Like REPLACE, but it looks for a pattern rather than exact text: [0-9] — any digit, ' +' — one or more spaces in a row. Without flags it replaces only the first occurrence; the 'g' flag replaces all of them, 'i' ignores case.
employees
| name | department | salary | bonus | phone |
|---|---|---|---|---|
| Emma | backend | 1200 | 300 | +44 7700 900123 |
| Liam | backend | 1000 | NULL | NULL |
| Olivia | frontend | 1350 | 150 | +44 7700 900456 |
| Noah | frontend | 950 | NULL | +44 7700 900789 |
| Sophia | design | 1200 | 200 | NULL |
These tables already exist in the sandbox — the examples below use them. Want to try this in your own database? Copy the SQL.
| hidden |
|---|
| Card **** **** |
| name | phone | digits |
|---|---|---|
| Emma | +44 7700 900123 | 447700900123 |
| Liam | NULL | NULL |
| Olivia | +44 7700 900456 | 447700900456 |
| Noah | +44 7700 900789 | 447700900789 |
| Sophia | NULL | NULL |
[^0-9] means any character except a digit: the plus and the spaces disappear.
| cleaned |
|---|
| Emma Wilson |
Without 'g', only the first occurrence is replaced: REGEXP_REPLACE('a1b2', '[0-9]', '') gives ab2.
A dot in a pattern means “any character”: REGEXP_REPLACE('v1.2.3', '.', '-', 'g') gives ------. Write the dot itself as '\.' — then you get v1-2-3.
To replace exact text, REPLACE is simpler and faster.