DuckDB GLOB Operator

DuckDB supports the GLOB operator for case-sensitive pattern matching on strings. It also provides a glob() table function that returns files matching a filesystem pattern. Both use the same wildcard syntax — * for zero or more characters, ? for exactly one, and [...] brackets for character sets. This post covers the operator, the table function, and the patterns that work in each.

Wildcards

The * wildcard

* matches zero or more characters. It is the most common pattern for suffix, prefix, or infix matching.

SELECT 'report.parquet' GLOB '*.parquet';     -- true (suffix match)
SELECT 'report.parquet' GLOB 'report*';       -- true (prefix match)
SELECT 'report_2025.parquet' GLOB 'report*parquet';  -- true (infix)
SELECT 'report' GLOB 'report*';              -- true (* matches empty string)
SELECT 'report.csv' GLOB '*.parquet';        -- false

The ? wildcard

? matches exactly one character — no more, no less.

SELECT 'a1' GLOB 'a?';    -- true (one character after 'a')
SELECT 'a' GLOB 'a?';     -- false (zero characters after 'a')
SELECT 'a12' GLOB 'a?';   -- false (two characters after 'a')
SELECT 'abc' GLOB '???';  -- true (exactly three characters)
SELECT 'ab' GLOB '???';   -- false (only two characters)

The [...] bracket

Brackets define a character set. The pattern matches if the character at that position is in the set. You can use ranges with -.

SELECT 'cat' GLOB '[abc]at';   -- true ('c' is in [abc])
SELECT 'dat' GLOB '[abc]at';   -- false ('d' is not in [abc])
SELECT 'f' GLOB '[a-z]';      -- true (lowercase range)
SELECT 'F' GLOB '[a-z]';      -- false (uppercase not in range)
SELECT 'F' GLOB '[a-zA-Z]';   -- true (combined ranges)
SELECT '5' GLOB '[0-9]';      -- true (digit range)
SELECT 'a' GLOB '[0-9]';      -- false

The [!...] negation

Prefixing the bracket contents with ! inverts the match — the character must not be in the set.

SELECT 'x' GLOB '[!abc]';   -- true ('x' is not in [abc])
SELECT 'a' GLOB '[!abc]';   -- false ('a' is in [abc])
SELECT 'A' GLOB '[!a-z]';   -- true (uppercase is not in [a-z])
SELECT 'a' GLOB '[!a-z]';   -- false

Case Sensitivity

Unlike LIKE, GLOB is always case-sensitive. There is no IGLOB.

SELECT 'NYC' GLOB 'nyc';  -- false
SELECT 'NYC' GLOB 'NYC';  -- true
SELECT 'NYC' GLOB 'n*';   -- false
SELECT 'NYC' GLOB 'N*';   -- true

Operator Aliases

DuckDB provides ~~~ as a synonym for GLOB, and NOT GLOB for negation:

SELECT 'test.txt' ~~~ '*.txt';        -- true
SELECT 'test.csv' ~~~ '*.txt';        -- false
SELECT 'a1' ~~~ '[a-z]?';             -- true
SELECT NOT 'test.csv' GLOB '*.txt';   -- true
SELECT NOT 'test.txt' GLOB '*.txt';   -- false

Combining Wildcards

The real power comes from combining *, ?, and [...] in a single pattern:

SELECT 'file_a.txt' GLOB 'file_?.tx*';       -- true
SELECT 'log_2025.csv' GLOB '[a-z]*_????.csv'; -- true
SELECT 'x9_data' GLOB '[a-z]?_*';            -- true
SELECT 'X9_data' GLOB '[a-z]?_*';            -- false (case-sensitive)

The pattern [a-z]*_????.csv reads as: starts with a lowercase letter, then any characters, then an underscore, then exactly four characters, then .csv. This matches filenames like log_2025.csv or data_0001.csv.

GLOB in WHERE Clauses

GLOB works in WHERE clauses just like any other predicate:

CREATE TABLE files (name VARCHAR);
INSERT INTO files VALUES
    ('report_2024.csv'), ('report_2025.csv'),
    ('data_jan.parquet'), ('data_feb.parquet'),
    ('README.md'), ('notes.txt');

SELECT name FROM files WHERE name GLOB '*.parquet';
-- data_jan.parquet
-- data_feb.parquet

SELECT name FROM files WHERE name GLOB '*_20??.csv';
-- report_2024.csv
-- report_2025.csv

SELECT name FROM files WHERE name GLOB '[a-z]*';
-- report_2024.csv, report_2025.csv, data_jan.parquet,
-- data_feb.parquet, notes.txt  (5 rows — all lowercase-starting names)

SELECT name FROM files WHERE name GLOB '[!a-z]*';
-- README.md  (only the uppercase-starting name)

The glob() Table Function

The glob() table function returns filesystem paths matching a pattern. It uses the same wildcard syntax as the GLOB operator but operates on the local filesystem instead of string values.

SELECT * FROM glob('*.parquet');
SELECT * FROM glob('data/year=*/month=*/*.parquet');
SELECT * FROM glob('/var/log/app-????-??-??.log');

This is useful for discovering files before reading them. DuckDB’s read_parquet() and read_csv() also accept glob patterns directly, so glob() is primarily for inspection:

SELECT count(*) FROM glob('data/**/*.parquet');

SELECT file FROM glob('exports/report_*.csv')
ORDER BY file DESC
LIMIT 5;

The ** pattern in glob() matches directories recursively, which is not available in the GLOB string operator.

Pattern Reference

Pattern	Meaning	Example Match
*	Zero or more characters	*.csv matches data.csv
?	Exactly one character	file_?.txt matches file_a.txt
[abc]	One character from the set	[abc]at matches cat
[a-z]	One character in the range	[a-z] matches f
[!abc]	One character NOT in the set	[!abc]at matches dat
[!a-z]	One character NOT in the range	[!a-z] matches F
~~~	GLOB synonym	name ~~~ '*.csv'