---
title: "regexp_full_match"
slug: "dev-database-reference-sql-functions-string-regexp-full-match"
updated: 2026-10-30T18:18:56Z
published: 2026-10-30T18:18:56Z
canonical: "docs.vastdata.com/dev-database-reference-sql-functions-string-regexp-full-match"
---

> ## 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_full_match

*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_full_match(s, pattern)` is true if the regular expression `pattern` matches **all** of `s`, from the first character to the last. `s ~ pattern` is the same test, and `s !~ pattern` its negation.

## Syntax

```
regexp_full_match(s, pattern)
regexp_full_match(s, pattern, options)
s ~ pattern
s !~ pattern
```

| Argument | Type | Description |
| --- | --- | --- |
| `s` | `VARCHAR` | The string to test. |
| `pattern` | `VARCHAR` | The regular expression ([syntax](/develop/docs/dev-database-reference-sql-functions-string-regexp-syntax)). A constant or a column. |
| `options` | `VARCHAR` | Optional, a constant. Flags such as `'i'` (ignore case) or `'s'` (`.` matches a line break); see [options](/develop/docs/dev-database-reference-sql-functions-string-regexp-syntax#what-it-does). The operators take no options. |

**Returns:** `BOOLEAN`.

**Aliases:** `~` (`regexp_full_match(s, pattern)`), `!~` (`NOT regexp_full_match(s, pattern)`).

## 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_full_match`. 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 five event codes. 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_full_match"'

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, "<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}.events"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, code VARCHAR)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, 'ERR-001'), "
    "(2, 'ERR-1002'), "
    "(3, 'WARN-001'), "
    "(4, 'err-003'), "
    "(5, NULL)")
```

```
[]
[]
[(5,)]
```

## What it does

The pattern must match the whole string, as if it were written `^(pattern)&#x24;`:

| Call | Result | Why |
| --- | --- | --- |
| `regexp_full_match(&apos;abc&apos;, &apos;b&apos;)` | `false` | `b` is only part of the string |
| `regexp_full_match(&apos;abc&apos;, &apos;.*b.*&apos;)` | `true` | the pattern covers the whole string |
| `regexp_full_match(&apos;abc&apos;, &apos;ab\\|abc&apos;)` | `true` | an alternative that matches the whole string is used |
| `regexp_full_match(&apos;ABC&apos;, &apos;abc&apos;, &apos;i&apos;)` | `true` | option `i` ignores case |
| `&apos;ABC&apos; ~ &apos;(?i)abc&apos;` | `true` | the operator takes no options; use an inline flag |

A trailing line break is part of the string: `&apos;a&apos;` followed by a line break doesn't fully match `&apos;a&apos;` or `&apos;a&#x24;&apos;`. The pattern syntax and the options are described on [Regular-expression syntax and options](/develop/docs/dev-database-reference-sql-functions-string-regexp-syntax).

## NULLs and edge cases

| Input | Result |
| --- | --- |
| A `NULL` string or pattern | `NULL` |
| A `NULL` options string | Error: `Regex options field must not be NULL` |
| `regexp_full_match(&apos;&apos;, &apos;&apos;)`, `regexp_full_match(&apos;&apos;, &apos;.*&apos;)` | `true` |
| `regexp_full_match(&apos;abc&apos;, &apos;&apos;)` | `false` |
| `regexp_full_match(&apos;&#xe9;&apos;, &apos;.&apos;)` (precomposed), `regexp_full_match(&apos;e\u0301&apos;, &apos;.&apos;)` | `true`, `false` |
| `&apos;abc&apos; !~ &apos;.*&apos;` | `false` |
| An invalid constant pattern: `&apos;abc&apos; ~ &apos;(&apos;` | Error: `missing ): (` |
| `&apos;abc&apos; ~ 1` | Error: `No function matches the given name and argument types &apos;regexp_full_match(STRING_LITERAL, INTEGER_LITERAL)&apos;` |

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-full-match#set-up-the-examples):

```
run("SELECT regexp_full_match(NULL, 'a'), 'abc' ~ NULL, NULL !~ 'a', regexp_full_match('', ''), "
    "regexp_full_match('', '.*'), regexp_full_match('abc', '')")
run("SELECT regexp_full_match('abc', 'abc', NULL)")
run("SELECT regexp_full_match('\u00e9', '.'), regexp_full_match('e\u0301', '.'), 'abc' !~ '.*'")
run("SELECT 'abc' ~ '('")
run("SELECT 'abc' ~ 1")
```

```
[(None, None, None, True, True, False)]
InternalError: Invalid Input Error: Regex options field must not be NULL
[(True, False, False)]
InternalError: Invalid Input Error: missing ): (
InternalError: Binder Error: No function matches the given name and argument types 'regexp_full_match(STRING_LITERAL, INTEGER_LITERAL)'. You might need to add explicit type casts.
```

## Examples

Which codes have the form `ERR-` and three digits: `ERR-1002` has four, and `err-003` is lower-case unless you add option `i`. `!~` gives the opposite:

```
run(f"SELECT id, regexp_full_match(code, 'ERR-\\d{{3}}'), regexp_full_match(code, 'ERR-\\d{{3}}', 'i'), "
    f"code ~ '[A-Z]+-\\d+', code !~ '[A-Z]+-\\d+' FROM {TABLE} ORDER BY id LIMIT 10")
```

```
[(1, True, True, True, False), (2, False, False, True, False), (3, False, False, True, False), (4, False, True, False, True), (5, None, None, None, None)]
```

Keep the rows whose code is well formed, in any case:

```
run(f"SELECT id, code FROM {TABLE} WHERE regexp_full_match(code, '[a-z]+-\\d{{3}}', 'i') ORDER BY id LIMIT 10")
```

```
[(1, 'ERR-001'), (3, 'WARN-001'), (4, 'err-003')]
```

## 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_full_match` is always evaluated in the Query Engine: in `WHERE`, it's a `FILTER` step above the scan, which sends the column for every row. `~` appears in plans as `regexp_full_match`, and `!~` as `NOT regexp_full_match`.

When the pattern starts with fixed text, the Query Engine adds a second `FILTER` step before the pattern test: a range on the column (cast to `BLOB` in the plan) that covers the strings the pattern can match. Both steps run in the Query Engine; the range isn't pushed down to the scan.

```
plan(f"SELECT id FROM {TABLE} WHERE code ~ 'ERR-\\d+'")
plan(f"SELECT id FROM {TABLE} WHERE code !~ 'ERR-\\d+'")
```

```
┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│  regexp_full_match(code,  │
│         'ERR-\d+')        │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│  ((code >= 'ERR-0'::BLOB) │
│   AND (code <= 'ERR-99:': │
│          :BLOB))          │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│            code           │
│             id            │
│                           │
└───────────────────────────┘
┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│   (NOT regexp_full_match  │
│     (code, 'ERR-\d+'))    │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│            code           │
│             id            │
│                           │
└───────────────────────────┘
```

## Make it faster

- **Use `=` for a fixed value.** `regexp_full_match(code, &apos;ERR-001&apos;)` is a `FILTER` step; `code = &apos;ERR-001&apos;` is pushed down to VAST DataBase. [regexp_matches](/develop/docs/dev-database-reference-sql-functions-string-regexp-matches) with `&apos;^ERR-001&#x24;&apos;` is rewritten to the same equality.
- **Use a range for the fixed start.** `code &gt;= &apos;ERR-&apos; AND code &lt; &apos;ERR.&apos;` is pushed down, so only the rows in the range reach the pattern test. Your range doesn't appear as a step in the plan; the two `FILTER` steps shown are the pattern's own, as above:

```
plan(f"SELECT id FROM {TABLE} WHERE code >= 'ERR-' AND code < 'ERR.' AND regexp_full_match(code, 'ERR-\\d{{3}}')")
run(f"SELECT id FROM {TABLE} WHERE code >= 'ERR-' AND code < 'ERR.' AND regexp_full_match(code, 'ERR-\\d{{3}}') "
    "ORDER BY id LIMIT 10")
```

```
┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│  regexp_full_match(code,  │
│        'ERR-\d{3}')       │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│ ((code >= 'ERR-000'::BLOB)│
│   AND (code <= 'ERR-999': │
│          :BLOB))          │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│            code           │
│             id            │
│                           │
└───────────────────────────┘
[(1,)]
```

- **Validate at load time.** If you check the form of a value on every query, check it once when you write the data and store the result in a `BOOLEAN` column.

## Limitations and errors

- **No case-insensitive operator.** `~*` and `!~*` fail with `Scalar Function with name ~* does not exist!`. Use `regexp_full_match(s, pattern, &apos;i&apos;)`, or `(?i)` in the pattern.
- **`~` binds more tightly than `||`.** `&apos;abc&apos; ~ &apos;a&apos; || &apos;bc&apos;` is `(&apos;abc&apos; ~ &apos;a&apos;) || &apos;bc&apos;`, the string `&apos;falsebc&apos;`. Put a built pattern in parentheses: `&apos;abc&apos; ~ (&apos;a&apos; || &apos;bc&apos;)`.
- **No `~ ANY` or `~ ALL`.** `s ~ ANY ([...])` fails with `Unsupported comparison &quot;~&quot; for ANY/ALL subquery`. Combine the tests with `OR`, or use one pattern with `|`.
- **`~` on a number is bitwise NOT.** `~ 5` is `-6`; see [bitwise NOT](/develop/docs/dev-database-reference-sql-functions-numeric-bitwise-not). As a binary operator, `~` takes strings only.
- **Some pattern features aren't available,** such as backreferences and lookahead: see [syntax and options](/develop/docs/dev-database-reference-sql-functions-string-regexp-syntax#limitations-and-errors).

```
run("SELECT 'abc' ~* 'B'")
run("SELECT 'abc' ~ 'a' || 'bc', 'abc' ~ ('a' || 'bc')")
run("SELECT 'abc' ~ ANY (['a.*', 'x'])")
run("SELECT regexp_full_match('abc', '.*B.*', 'i'), 'abc' ~ 'x|a.*', ~ 5")
```

```
InternalError: Catalog Error: Scalar Function with name ~* does not exist!
[('falsebc', True)]
InternalError: Parse query: 'SELECT 'abc' ~ ANY (['a.*', 'x'])' failed with: Parser Error: Unsupported comparison "~" for ANY/ALL subquery
[(True, True, -6)]
```

## Similar functions

| Function | Tests | Result for `&apos;abc&apos;` and `a.c` |
| --- | --- | --- |
| `regexp_full_match(&apos;abc&apos;, &apos;a.c&apos;)`, `~` | a regular expression, the whole string | `true` |
| `NOT (&apos;abc&apos; !~ &apos;a.c&apos;)` | the same, negated twice | `true` |
| [SIMILAR TO](/develop/docs/dev-database-reference-sql-functions-string-similar-to) `&apos;a.c&apos;` | a regular expression, the whole string | `true` |
| [regexp_matches](/develop/docs/dev-database-reference-sql-functions-string-regexp-matches)`(&apos;abc&apos;, &apos;^a.c&#x24;&apos;)` | a regular expression, anywhere unless anchored | `true` |
| [LIKE](/develop/docs/dev-database-reference-sql-functions-string-like) `&apos;a_c&apos;` | `%` and `_` wildcards, the whole string | `true` |

```
run("SELECT regexp_full_match('abc', 'a.c'), 'abc' ~ 'a.c', NOT ('abc' !~ 'a.c'), 'abc' SIMILAR TO 'a.c', "
    "regexp_matches('abc', '^a.c$'), 'abc' LIKE 'a_c'")
run("SELECT regexp_full_match('xabcx', 'abc'), regexp_matches('xabcx', 'abc')")
```

```
[(True, True, True, True, True, True)]
[(False, True)]
```

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.