SQLite GLOB: Case-Sensitive Pattern Matching with Wildcards (Tested)

Ninad Pathak

October 7, 2026

In a Unix shell, *.csv means anything ending in .csv, and SQLite’s GLOB reads patterns the same way. Unlike LIKE, it’s case-sensitive and uses different wildcards. In this article I’ll walk you through every GLOB wildcard with real output, side by side with LIKE.

TLDR

GLOB matches text against a Unix-style pattern: * is any run of characters, and ? is one character.
[abc] matches one of the listed characters, and [a-z] matches one in a range. [^...] matches one character that isn’t in the set.

GLOB is case-sensitive. LIKE ignores case for ASCII letters.
A pattern must match the whole string, so use *text* to find text anywhere.
To match a literal * or ?, put it in brackets: [*].

What is SQLite GLOB?

GLOB is an operator that tests whether a text value matches a pattern. SQLite’s documentation says it “is similar to LIKE but uses the Unix file globbing syntax for its wildcards. Also, GLOB is case sensitive, unlike LIKE” (SQLite expressions).

It returns 1 when the value matches and 0 when it doesn’t, so you use it in a WHERE clause like any other condition.

SQLite GLOB syntax

expression [NOT] GLOB 'pattern'
glob('pattern', expression)

Wildcard	Matches	Example pattern	Matches
`*`	Any number of characters, including none	`'*.csv'`	`data.csv`, `.csv`
`?`	Exactly one character	`'img?.png'`	`img1.png`, not `img10.png`
`[abc]`	One character from the list	`'[Rr]eport*'`	`report.pdf`, `Report.PDF`
`[a-z]`	One character in the range	`'data_202[0-4]*'`	`data_2024.csv`
`[^abc]`	One character not in the list	`'data_202[^5]*'`	`data_2024.csv`, not `data_2025.csv`

How to filter rows with GLOB

To show you each wildcard, I will use a table of file names.

Step 1: Create the table

sqlite> CREATE TABLE files (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
sqlite> INSERT INTO files (name) VALUES ('report.pdf'), ('Report.PDF'), ('notes.txt'), ('data_2024.csv'), ('data_2025.csv'), ('img1.png'), ('img10.png'), ('photo*.jpg'), ('readme');

Step 2: Match any run of characters with *

sqlite> SELECT name FROM files WHERE name GLOB '*.csv';
name
-------------
data_2024.csv
data_2025.csv

Step 3: Match exactly one character with ?

img10.png doesn’t match, because ? stands for one character, not two:

sqlite> SELECT name FROM files WHERE name GLOB 'img?.png';
name
--------
img1.png

Examples

Each example below queries the same files table.

GLOB vs LIKE: case sensitivity

GLOB only finds the lowercase name. LIKE finds both, because it ignores case for ASCII letters (SQLite LIKE):

sqlite> SELECT name FROM files WHERE name GLOB 'report*';
name
----------
report.pdf
sqlite> SELECT name FROM files WHERE name LIKE 'report%';
name
----------
report.pdf
Report.PDF

Match either case with a character set

A set like [Rr] lets GLOB accept both cases for one letter:

sqlite> SELECT name FROM files WHERE name GLOB '[Rr]eport.*';
name
----------
report.pdf
Report.PDF

Match a range of characters

sqlite> SELECT name FROM files WHERE name GLOB 'data_202[0-4].csv';
name
-------------
data_2024.csv

[0-9] followed by * matches names with a digit and anything after it:

sqlite> SELECT name FROM files WHERE name GLOB 'img[0-9]*.png';
name
---------
img1.png
img10.png

Exclude characters with [^…]

sqlite> SELECT name FROM files WHERE name GLOB 'data_202[^5].csv';
name
-------------
data_2024.csv

Find rows that don’t match with NOT GLOB

This finds names with no dot in them:

sqlite> SELECT name FROM files WHERE name NOT GLOB '*.*';
name
------
readme

Match a literal * or ?

GLOB has no escape character. Put the special character in brackets instead:

sqlite> SELECT name FROM files WHERE name GLOB '*[*]*';
name
----------
photo*.jpg

Call GLOB as a function

The operator X GLOB Y is the function glob(Y, X), with the pattern first (SQLite core functions):

sqlite> SELECT glob('*.txt', 'notes.txt') AS fn, 'notes.txt' GLOB '*.txt' AS op;
fn  op
--  --
1   1

Edge cases

These two trip people up when they switch between GLOB and LIKE.

The pattern must match the whole string

Neither GLOB nor LIKE matches part of a string unless the pattern says so. Wrap the text in * to find it anywhere:

sqlite> SELECT 'report.pdf' GLOB 'report' AS glob_no_wildcard, 'report.pdf' LIKE 'report' AS like_no_wildcard, 'report.pdf' GLOB '*port*' AS glob_contains;
glob_no_wildcard  like_no_wildcard  glob_contains
----------------  ----------------  -------------
0                 0                 1

GLOB can use an index, LIKE might not

When a GLOB pattern starts with fixed characters, SQLite can use an index on the column to narrow the search. A pattern that starts with a wildcard reads every row. Here, LIKE scans too, because the index uses the default case-sensitive collation and LIKE is case-insensitive (The LIKE optimization):

sqlite> CREATE INDEX idx_files_name ON files(name);
sqlite> EXPLAIN QUERY PLAN SELECT name FROM files WHERE name GLOB 'data*';
QUERY PLAN
`--SEARCH files USING COVERING INDEX idx_files_name (name>? AND name<?)
sqlite> EXPLAIN QUERY PLAN SELECT name FROM files WHERE name GLOB '*.csv';
QUERY PLAN
`--SCAN files
sqlite> EXPLAIN QUERY PLAN SELECT name FROM files WHERE name LIKE 'data%';
QUERY PLAN
`--SCAN files

For GLOB, the docs require the column to use the built-in BINARY collation, which is the default.

Conclusion

Use GLOB when case matters, or when you want file-style patterns with character sets like [0-9]. Use LIKE when you want case-insensitive matching. For substrings, both need a wildcard on each side. For slicing strings rather than matching them, see SQLite substr().