--- title: "regexp_replace" slug: "dev-database-reference-sql-functions-string-regexp-replace" updated: 2026-10-30T18:18:56Z published: 2026-10-30T18:18:56Z canonical: "docs.vastdata.com/dev-database-reference-sql-functions-string-regexp-replace" --- > ## Documentation Index > Fetch the complete documentation index at: https://docs.vastdata.com/llms.txt > Use this file to discover all available pages before exploring further. # regexp_replace *Applies to VAST Cluster 5.5.* *[Functions](/develop/docs/dev-database-reference-sql-functions-index) › [Regular expressions](/develop/docs/dev-database-reference-sql-functions-string-regular-expressions) › Regular expressions.* `regexp_replace(s, pattern, replacement)` replaces the **first** part of `s` that the regular expression `pattern` matches with `replacement`. With option `'g'` it replaces every match. The replacement can refer to the groups of the match as `\1` to `\9`. ## Syntax ``` regexp_replace(s, pattern, replacement) regexp_replace(s, pattern, replacement, options) ``` | Argument | Type | Description | | --- | --- | --- | | `s` | `VARCHAR` | The string to change. | | `pattern` | `VARCHAR` | The regular expression ([syntax](/develop/docs/dev-database-reference-sql-functions-string-regexp-syntax)). A constant or a column. | | `replacement` | `VARCHAR` | The text to put in place of each match. `\0` is the whole match, `\1` to `\9` its groups, and `\\` a backslash. A constant or a column. | | `options` | `VARCHAR` | Optional, a constant. `'g'` replaces every match; other flags such as `'i'` (ignore case) or `'l'` (literal pattern) are described on [options](/develop/docs/dev-database-reference-sql-functions-string-regexp-syntax#what-it-does). | **Returns:** `VARCHAR`. ## Set up the examples The examples connect with the ADBC driver, as in [VAST Query Engine (ADBC)](/develop/docs/dev-database-integrations-adbc), and work in a schema called `docs_sql_fn_string_regexp_replace`. Set `QE_ENDPOINT`, `AWS_ACCESS_KEY_ID`, `AWS_SECRET_ACCESS_KEY` and `VASTDB_BUCKET` ([SQL reference](/develop/docs/dev-database-reference-sql-index#example-connect-and-use-a-transaction)), and run the blocks in order in one Python session. Below each block is the output you should see. This block connects, defines the `run()` and `plan()` helpers, and creates a table of four contacts with a name and a phone number. Contact 3 has empty values, and the last row has no values: ``` import os import re import adbc_driver_manager import adbc_driver_manager.dbapi as dbapi import adbc_driver_vastdb BUCKET = os.environ["VASTDB_BUCKET"] SCHEMA = f'"{BUCKET}/docs_sql_fn_string_regexp_replace"' conn = dbapi.connect( driver=adbc_driver_vastdb.get_driver_path(), db_kwargs={ "vast.db.endpoint": os.environ["QE_ENDPOINT"], "vast.db.access_key": os.environ["AWS_ACCESS_KEY_ID"], "vast.db.secret_key": os.environ["AWS_SECRET_ACCESS_KEY"], }, autocommit=True, ) def server_message(e): """The server's message in a driver error, without the request ID or the echoed statement.""" text = re.sub(r"\[req=0x[0-9a-f]+\]: ", "", str(e)) found = (re.search(r'message: "(.*?)", request_id', text, re.S) or re.search(r"description: (.*)\)", text, re.S)) message = found.group(1) if found else text.strip().splitlines()[-1] message = re.sub(r"^Plan query: '.*?' failed with: ", "", message, flags=re.S) message = re.split(r"\\n|\n", message)[0].replace('\\"', '"') return message.replace(BUCKET, "") def run(sql): """Run one statement. Print the rows it returns, or the error.""" try: with conn.cursor() as cur: cur.execute(sql) rows = cur.fetchall() if cur.description else [] except (adbc_driver_manager.Error, OSError) as e: print(f"{type(e).__name__}: {server_message(e)}") else: print(rows) def plan(sql): """Print the plan without its ~N Rows lines: the table's row count, which can still be 0 just after an insert.""" with conn.cursor() as cur: cur.execute("EXPLAIN " + sql) steps = cur.fetchall()[0][1] print("\n".join(line for line in steps.splitlines() if "Rows" not in line)) TABLE = f"{SCHEMA}.contacts" run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}") run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, name VARCHAR, phone VARCHAR)") run(f"INSERT INTO {TABLE} VALUES " "(1, 'Ada Lovelace', '+44 20 7946 0000'), " "(2, 'Grace Hopper', '(212) 555-0100'), " "(3, '', ''), " "(4, NULL, NULL)") ``` ``` [] [] [(4,)] ``` ## What it does | Call | Result | Why | | --- | --- | --- | | `regexp_replace('aaa', 'a', 'b')` | `'baa'` | only the first match is replaced | | `regexp_replace('aaa', 'a', 'b', 'g')` | `'bbb'` | option `g`: every match | | `regexp_replace('John Smith', '(\w+) (\w+)', '\2, \1')` | `'Smith, John'` | `\1` and `\2` are the groups | | `regexp_replace('abc', '(b)', '[\0]')` | `'a[b]c'` | `\0` is the whole match | | `regexp_replace('AaA', 'a', 'x', 'gi')` | `'xxx'` | options combine | | `regexp_replace('a.b.c', '.', '/')` | `'/.b.c'` | `.` is any character; escape it as `\.`, or use option `l` | In the replacement, a backslash starts a group reference: `\0` to `\9`, or `\\` for a backslash. Every other character, `$` and `&` included, is literal: `'$1'` puts `$1` in the result. A pattern that matches an empty string inserts the replacement: `regexp_replace('abc', '', '-', 'g')` is `'-a-b-c-'`, and `'^'` or `'$'` add text at the start or the end. ## NULLs and edge cases | Input | Result | | --- | --- | | `NULL` as any argument, the options included | `NULL` | | `regexp_replace('abc', 'x', 'y')` (no match) | `'abc'` | | `regexp_replace('abc', '', '-')` | `'-abc'` | | `regexp_replace('abc', '(b)', '[\2]')` (the pattern has one group) | `'abc'`: nothing is replaced | | `regexp_replace('abc', 'b', '\')` (a lone backslash) | `'abc'`: nothing is replaced | | `regexp_replace('abc', 'b', '\\')` | `'a\c'` | | `regexp_replace('cafe\u0301', '\u0301', '')` (remove the combining accent) | `'cafe'` | | `regexp_replace('abc', '(', 'x')` (an invalid pattern) | `'abc'`, with no error | | `regexp_replace('abc', 'b', 'x', 'G')` | Error: `Unrecognized Regex option G` | | `regexp_replace('abc', 'b', 1)` | Error: `No function matches the given name and argument types 'regexp_replace(STRING_LITERAL, STRING_LITERAL, INTEGER_LITERAL)'` | The same cases, as the cluster returns them. They use the table and helpers from [Set up the examples](/develop/docs/dev-database-reference-sql-functions-string-regexp-replace#set-up-the-examples): ``` run("SELECT regexp_replace(NULL, 'a', 'b'), regexp_replace('abc', NULL, 'x'), regexp_replace('abc', 'b', NULL), " "regexp_replace('abc', 'b', 'x', NULL)") run("SELECT regexp_replace('abc', 'x', 'y'), regexp_replace('abc', '', '-'), regexp_replace('abc', '(b)', '[\\2]'), " "regexp_replace('abc', 'b', '\\'), regexp_replace('abc', 'b', '\\\\')") run("SELECT regexp_replace('cafe\u0301', '\u0301', ''), regexp_replace('abc', '(', 'x')") run("SELECT regexp_replace('abc', 'b', 'x', 'G')") run("SELECT regexp_replace('abc', 'b', 1)") ``` ``` [(None, None, None, None)] [('abc', '-abc', 'abc', 'abc', 'a\\c')] [('cafe', 'abc')] InternalError: Invalid Input Error: Unrecognized Regex option G InternalError: Binder Error: No function matches the given name and argument types 'regexp_replace(STRING_LITERAL, STRING_LITERAL, INTEGER_LITERAL)'. You might need to add explicit type casts. ``` ## Examples Keep only the digits of each phone number, collapse runs of spaces in each name, and turn `First Last` into `Last, First`: ``` run(f"SELECT id, regexp_replace(phone, '\\D', '', 'g'), regexp_replace(name, '\\s+', ' ', 'g'), " f"regexp_replace(name, '^(\\w+)\\s+(\\w+)$', '\\2, \\1') FROM {TABLE} ORDER BY id LIMIT 10") ``` ``` [(1, '442079460000', 'Ada Lovelace', 'Lovelace, Ada'), (2, '2125550100', 'Grace Hopper', 'Hopper, Grace'), (3, '', '', ''), (4, None, None, None)] ``` Without `'g'`, only the first match changes. With `'i'`, case is ignored: ``` run(f"SELECT id, regexp_replace(phone, '\\d', '#'), regexp_replace(phone, '\\d', '#', 'g'), " f"regexp_replace(name, 'a', '_', 'gi') FROM {TABLE} ORDER BY id LIMIT 10") ``` ``` [(1, '+#4 20 7946 0000', '+## ## #### ####', '_d_ Lovel_ce'), (2, '(#12) 555-0100', '(###) ###-####', 'Gr_ce Hopper'), (3, '', '', ''), (4, None, None, None)] ``` Prefix every line of a multi-line string. `^` matches only at the start of the string unless the pattern has `(?m)`: ``` run("SELECT regexp_replace('a\nb\nc', '^', '> ', 'g'), regexp_replace('a\nb\nc', '(?m)^', '> ', 'g')") ``` ``` [('> a\nb\nc', '> a\n> b\n> c')] ``` ## How it runs in VAST The Query Engine runs on the cluster's CNodes, and a query on a table starts with a scan in VAST DataBase (`VAST_SCAN`). `regexp_replace` is always evaluated in the Query Engine: - **In the `SELECT` list,** the scan reads the column and the Query Engine rewrites each value it returns. - **In `WHERE`,** the scan sends the column for every row, and a `FILTER` step computes `regexp_replace()` and keeps the rows that match. ``` plan(f"SELECT id FROM {TABLE} WHERE regexp_replace(phone, '\\D', '', 'g') = '2125550100'") ``` ``` ┌───────────────────────────┐ │ PROJECTION │ │ ──────────────────── │ │ #1 │ │ │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ FILTER │ │ ──────────────────── │ │ (regexp_replace(phone, '\D│ │', '', 'g') = '2125550100')│ │ │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ VAST_SCAN │ │ ──────────────────── │ │ Function: VAST_SCAN │ │ │ │ Projections: │ │ phone │ │ id │ │ │ └───────────────────────────┘ ``` ## Make it faster - **Use [replace](/develop/docs/dev-database-reference-sql-functions-string-replace) or [translate](/develop/docs/dev-database-reference-sql-functions-string-translate) for literal text.** `replace(s, '.', '/')` replaces every `.` without a regular expression; `regexp_replace` needs `'\.'` and `'g'` for the same result. - **Normalise at load time.** If you compare a cleaned-up form often, such as the digits of a phone number, store it in its own column when you write the data. A comparison of that column with a constant is pushed down to VAST DataBase. - **Combine it with a pushed-down condition.** In `id > 100 AND regexp_replace(...) = '...'`, `id > 100` is still evaluated in VAST DataBase, so the replacement runs only on the rows that pass it. ## Limitations and errors - **An invalid replacement changes nothing.** A reference to a group the pattern doesn't have, such as `\2` with one group, a lone backslash, `\n` or `\g<name>`, returns `s` unchanged. Check the replacement on a sample first. - **An invalid constant pattern returns `s` unchanged** instead of failing. Check a pattern with [regexp_matches](/develop/docs/dev-database-reference-sql-functions-string-regexp-matches), which reports the error. - **The options string is a constant.** From a column it fails with `Regex options field must be a constant`. Options are lower-case: `'G'` fails with `Unrecognized Regex option G`. - **No `regexp_replace_all` or `replace_regex`.** Both fail with `Scalar Function with name ... does not exist!`. Use `regexp_replace(s, pattern, replacement, 'g')`. ``` run("SELECT regexp_replace('abc', 'b', '\\n'), regexp_replace('abc', '(?Pb)', '\\g')") run(f"SELECT regexp_replace(name, 'a', '_', CAST(id AS VARCHAR)) FROM {TABLE} LIMIT 1") run("SELECT regexp_replace_all('aa', 'a', 'b')") run("SELECT regexp_replace('aa', 'a', 'b', 'g')") ``` ``` [('abc', 'abc')] InternalError: Invalid Input Error: Regex options field must be a constant InternalError: Catalog Error: Scalar Function with name regexp_replace_all does not exist! [('bb',)] ``` ## Similar functions | Function | Replaces | Result for `'a.b.c'`, `.` by `/` | | --- | --- | --- | | `regexp_replace('a.b.c', '.', '/')` | the first match of a regular expression | `'/.b.c'` | | `regexp_replace('a.b.c', '\.', '/', 'g')` | every match | `'a/b/c'` | | [replace](/develop/docs/dev-database-reference-sql-functions-string-replace)`('a.b.c', '.', '/')` | every occurrence of literal text | `'a/b/c'` | | [translate](/develop/docs/dev-database-reference-sql-functions-string-translate)`('a.b.c', '.', '/')` | single characters, one for one | `'a/b/c'` | | [trim](/develop/docs/dev-database-reference-sql-functions-string-trim), [ltrim](/develop/docs/dev-database-reference-sql-functions-string-ltrim), [rtrim](/develop/docs/dev-database-reference-sql-functions-string-rtrim) | characters at the ends | (see their pages) | ``` run("SELECT regexp_replace('a.b.c', '.', '/'), regexp_replace('a.b.c', '\\.', '/', 'g'), " "replace('a.b.c', '.', '/'), translate('a.b.c', '.', '/')") run("SELECT trim(' a '), regexp_replace(' a ', '^\\s+|\\s+$', '', 'g')") ``` ``` [('/.b.c', 'a/b/c', 'a/b/c', 'a/b/c')] [('a', 'a')] ``` Clean up: ``` run(f"DROP TABLE IF EXISTS {TABLE}") run(f"DROP SCHEMA IF EXISTS {SCHEMA}") conn.close() ``` ``` [] [] ``` ## See also - [Regular-expression syntax and options](/develop/docs/dev-database-reference-sql-functions-string-regexp-syntax). - [Regular expressions](/develop/docs/dev-database-reference-sql-functions-string-regular-expressions): all the regular-expression functions. - [Where filters run](/develop/docs/dev-database-reference-sql-select#where-filters-run) and [pushdowns](/develop/docs/dev-database-reference-pushdowns). - [EXPLAIN](/develop/docs/dev-database-reference-sql-explain): how to read a plan.