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

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

# GLOB

*Applies to VAST Cluster 5.5.*

*[Functions](/develop/docs/dev-database-reference-sql-functions-index) › [Search and split](/develop/docs/dev-database-reference-sql-functions-string-search-and-split) › Pattern matching.* `s GLOB pattern` is true if the whole string matches a shell-style pattern: `*` stands for any run of characters, `?` for one, and `[...]` for one of a set. It's case-sensitive. `~~~` is the operator form, and the name that plans and errors show. `NOT GLOB` isn't available: write `NOT (s GLOB pattern)`.

## Syntax

```
s GLOB pattern
s ~~~ pattern
NOT (s GLOB pattern)
```

| Argument | Type | Description |
| --- | --- | --- |
| `s` | `VARCHAR` | The string to test. |
| `pattern` | `VARCHAR` | The pattern. `*` matches any run of characters, including none; `?` matches one character; `[...]` matches one character of a set. |

**Returns:** `BOOLEAN`.

**Aliases:** `~~~`.

## 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_glob`. 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 file paths. Row 2 has an upper-case extension. 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_glob"'

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}.files"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, path VARCHAR)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, '/data/logs/app.log'), "
    "(2, '/data/logs/db.LOG'), "
    "(3, '/data/tmp/run1.txt'), "
    "(4, '/data/tmp/run12.txt'), "
    "(5, NULL)")
```

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

## What it does

The pattern must match the **whole** string, and letters match only the same case.

| In the pattern | Matches | Example | Result |
| --- | --- | --- | --- |
| `*` | any run of characters, including none and including `/` | `&apos;a/b/c&apos; GLOB &apos;a/*/c&apos;` | `true` |
| `?` | exactly one character | `&apos;abc&apos; GLOB &apos;a?c&apos;` | `true` |
| `[abc]`, `[a-c]` | one character of the set or range | `&apos;abc&apos; GLOB &apos;[a-c]*&apos;` | `true` |
| `[!a]` | one character not in the set | `&apos;bbc&apos; GLOB &apos;[!a]*&apos;` | `true` |
| `[*]`, `[?]` | that character literally | `&apos;a?c&apos; GLOB &apos;a[?]c&apos;` | `true` |
| `\*` | a literal `*` | `&apos;a*c&apos; GLOB &apos;a\*c&apos;` | `true` |
| any other character | itself, case-sensitive | `&apos;abc&apos; GLOB &apos;A*&apos;` | `false` |

- **`%` and `_` are ordinary characters,** unlike in [LIKE](/develop/docs/dev-database-reference-sql-functions-string-like).
- **`^` doesn't negate a set.** `&apos;abc&apos; GLOB &apos;[^a]*&apos;` is true: `[^a]` is the set of `^` and `a`. Use `[!a]`.
- **A malformed set doesn't fail.** `&apos;abc&apos; GLOB &apos;[a-&apos;` and `&apos;abc&apos; GLOB &apos;[]&apos;` are false.
- **`?` doesn't match a non-ASCII character such as `&#xe9;`,** whether it's precomposed or written with a combining accent, and `??` doesn't match a flag emoji. For one character of any script, use [LIKE](/develop/docs/dev-database-reference-sql-functions-string-like) `&apos;_&apos;`.
- **`*` and `?` match a line break.**

## NULLs and edge cases

| Input | Result |
| --- | --- |
| `NULL GLOB &apos;H*&apos;`, `&apos;abc&apos; GLOB NULL`, `NULL GLOB NULL` | `NULL` |
| `&apos;&apos; GLOB &apos;*&apos;`, `&apos;&apos; GLOB &apos;&apos;` | `true` |
| `&apos;abc&apos; GLOB &apos;&apos;` | `false` |
| `&apos;abc&apos; GLOB &apos;a??c&apos;` | `false` |
| `&apos;abc&apos; GLOB &apos;A*&apos;` | `false` |
| `&apos;abc&apos; GLOB &apos;[!a]*&apos;`, `&apos;abc&apos; GLOB &apos;[^a]*&apos;` | `false`, `true` |
| `&apos;abc&apos; GLOB &apos;[a-&apos;`, `&apos;abc&apos; GLOB &apos;[]&apos;` | `false` |
| `&apos;a*c&apos; GLOB &apos;a\*c&apos;`, `&apos;abc&apos; GLOB &apos;a\*c&apos;` | `true`, `false` |
| `&apos;a%c&apos; GLOB &apos;a%c&apos;`, `&apos;abc&apos; GLOB &apos;a%&apos;` | `true`, `false` |
| `&apos;&#xe9;&apos; GLOB &apos;?&apos;` | `false` |
| A line break: `&apos;a` + line break + `b&apos; GLOB &apos;a?b&apos;` | `true` |
| A number, date, boolean, `BLOB` or list | Error: `No function matches the given name and argument types &apos;~~~(INTEGER_LITERAL, STRING_LITERAL)&apos;` (for a number) |

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

```
run("SELECT NULL GLOB 'H*', 'abc' GLOB NULL, NULL GLOB NULL, '' GLOB '*', '' GLOB '', 'abc' GLOB '', "
    "'abc' GLOB 'a??c', 'abc' GLOB 'A*'")
run("SELECT 'abc' GLOB '[!a]*', 'abc' GLOB '[^a]*', 'abc' GLOB '[a-', 'abc' GLOB '[]'")
run("SELECT 'a*c' GLOB 'a\\*c', 'abc' GLOB 'a\\*c', 'a%c' GLOB 'a%c', 'abc' GLOB 'a%'")
run("SELECT '\u00e9' GLOB '?', 'a\nb' GLOB 'a?b'")
run("SELECT 123 GLOB 'H*'")
```

```
[(None, None, None, True, True, False, False, False)]
[(False, True, False, False)]
[(True, False, True, False)]
[(False, True)]
InternalError: Binder Error: No function matches the given name and argument types '~~~(INTEGER_LITERAL, STRING_LITERAL)'. You might need to add explicit type casts.
```

## Examples

Match the log files, the files in any subdirectory with a `.txt` extension, and a one-digit run number. The row with no path gives `NULL`:

```
run(f"SELECT id, path GLOB '/data/logs/*.log', path GLOB '/data/*/*.txt', path GLOB '*/run[0-9].txt' "
    f"FROM {TABLE} ORDER BY id LIMIT 10")
```

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

`GLOB` is case-sensitive. To accept either case of an extension, list both in sets:

```
run(f"SELECT id, path FROM {TABLE} WHERE path GLOB '*.[lL][oO][gG]' ORDER BY id LIMIT 10")
```

```
[(1, '/data/logs/app.log'), (2, '/data/logs/db.LOG')]
```

Keep the rows that don't match, and use the operator form:

```
run(f"SELECT id, NOT (path GLOB '/data/logs/*'), path ~~~ '/data/tmp/*' FROM {TABLE} ORDER BY id LIMIT 10")
```

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

## 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`). VAST DataBase can evaluate a comparison of a column with a constant, but not a pattern match, so `GLOB` is always computed by the Query Engine:

- **In the `SELECT` list,** the scan reads the column and the Query Engine evaluates `GLOB` for each row it returns.
- **In `WHERE`,** the scan sends the column for every row, and a `FILTER` step in the Query Engine keeps the rows that match. No rows are skipped in storage.

Unlike `LIKE`, `GLOB` isn't rewritten: a prefix pattern such as `&apos;/data/logs/*&apos;` and a pattern with no wildcard both stay `s ~~~ pattern` in the `FILTER` step. The plans:

```
plan(f"SELECT id FROM {TABLE} WHERE path GLOB '/data/logs/*'")
plan(f"SELECT id FROM {TABLE} WHERE path GLOB '/data/logs/app.log'")
```

```
┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│ (path ~~~ '/data/logs/*') │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│            path           │
│             id            │
│                           │
└───────────────────────────┘
┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│ (path ~~~ '/data/logs/app │
│           .log')          │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│            path           │
│             id            │
│                           │
└───────────────────────────┘
```

## Make it faster

- **Compare whole values with `=`.** `path = &apos;/data/logs/app.log&apos;` is pushed down to VAST DataBase; the same test written with `GLOB` isn't.
- **Use a range for a prefix.** `path &gt;= &apos;/data/logs/&apos; AND path &lt; &apos;/data/logs0&apos;` is pushed down; `GLOB &apos;/data/logs/*&apos;` and `LIKE &apos;/data/logs/%&apos;` run in the `FILTER` step. The plan has no `FILTER` step:

```
plan(f"SELECT id FROM {TABLE} WHERE path >= '/data/logs/' AND path < '/data/logs0'")
```

```
┌───────────────────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│      Projections: id      │
│                           │
└───────────────────────────┘
```

- **Combine it with a pushed-down condition.** In `id &gt; 1 AND path GLOB &apos;*.txt&apos;`, `id &gt; 1` is still evaluated in VAST DataBase, so `GLOB` runs only on the rows that pass it.

## Limitations and errors

- **No `NOT GLOB`.** `s NOT GLOB pattern` fails with `Parser Error: syntax error at or near &quot;GLOB&quot;`, and `!~~~` fails with `Scalar Function with name !~~~ does not exist!`. Write `NOT (s GLOB pattern)`.
- **No `ESCAPE`.** `s GLOB pattern ESCAPE &apos;&#x24;&apos;` is a syntax error. Put a wildcard in a set, `[*]` or `[?]`, to match it literally.
- **No `glob()` scalar function.** `glob(&apos;abc&apos;, &apos;a*&apos;)` fails with `Function &quot;glob&quot; is a table function but it was used as a scalar function`; the `glob` table function is called in a `FROM` clause.
- **Strings only.** A number, a date, a boolean, a `BLOB` or a list fails with `No function matches the given name and argument types &apos;~~~(...)&apos;`. Cast the value to `VARCHAR` first.

```
run("SELECT 'abc' NOT GLOB 'x*'")
run("SELECT 'abc' GLOB 'a*' ESCAPE '$'")
run("SELECT glob('abc', 'a*')")
run("SELECT NOT ('abc' GLOB 'x*'), CAST(2026 AS VARCHAR) GLOB '20*'")
```

```
InternalError: Parse query: 'SELECT 'abc' NOT GLOB 'x*'' failed with: Parser Error: syntax error at or near "GLOB"
InternalError: Parse query: 'SELECT 'abc' GLOB 'a*' ESCAPE '$'' failed with: Parser Error: syntax error at or near "ESCAPE"
InternalError: Binder Error: Function "glob" is a table function but it was used as a scalar function. This function has to be called in a FROM clause (similar to a table).
[(True, True)]
```

## Similar functions

| Function | Any run | One character | Set | Case | Pushed down |
| --- | --- | --- | --- | --- | --- |
| `GLOB` | `*` | `?` | `[...]`, `[!...]` | sensitive | never |
| [LIKE](/develop/docs/dev-database-reference-sql-functions-string-like) | `%` | `_` | none | sensitive | without wildcards |
| [ILIKE](/develop/docs/dev-database-reference-sql-functions-string-ilike) | `%` | `_` | none | ignored | never |
| [SIMILAR TO](/develop/docs/dev-database-reference-sql-functions-string-similar-to) | `.*` | `.` | regular-expression classes | sensitive | never |
| [regexp_matches](/develop/docs/dev-database-reference-sql-functions-string-regexp-matches) | `.*` | `.` | regular-expression classes | sensitive, or `&apos;i&apos;` | an anchored whole value, `&apos;^abc&#x24;&apos;` |

```
run("SELECT 'abc' GLOB 'a*', 'abc' LIKE 'a%', 'abc' ILIKE 'A%', 'abc' SIMILAR TO 'a.*', regexp_matches('abc', '^a')")
run("SELECT 'abc' GLOB 'a?c', 'abc' LIKE 'a_c', 'abc' SIMILAR TO 'a.c', regexp_matches('abc', '^a.c$')")
```

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

Clean up:

```
run(f"DROP TABLE IF EXISTS {TABLE}")
run(f"DROP SCHEMA IF EXISTS {SCHEMA}")
conn.close()
```

```
[]
[]
```

## See also

- [Search and split](/develop/docs/dev-database-reference-sql-functions-string-search-and-split): all the search and pattern functions.
- [Operators and predicates](/develop/docs/dev-database-reference-sql-functions-operators-and-predicates#pattern-matching).
- [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.