Most people learning SQL get introduced to pattern matching through the LIKE operator, and honestly, for a long time that’s the only pattern-matching tool most of us think we need. But SQLite has a second, less commonly discussed pattern-matching operator called GLOB, and once I actually took the time to learn it properly, I found myself reaching for it constantly — especially when I needed case-sensitive matching or Unix-shell-style wildcards. In this guide, I’ll walk through exactly what GLOB does, how its syntax differs from LIKE, and when you should actually choose one over the other.
What GLOB Does
GLOB is a pattern-matching operator in SQLite that works similarly to the wildcard matching used in Unix shell filename globbing (the same mechanism behind commands like ls *.txt). It lets you filter rows where a text column matches a specified pattern, using wildcard characters.
The basic syntax is:
SELECT column1, column2
FROM table_name
WHERE column_name GLOB 'pattern';
Here’s a simple example. Suppose I have a products table and I want every product name that starts with “Pro”:
SELECT product_name
FROM products
WHERE product_name GLOB 'Pro*';
This returns rows like “Pro Camera,” “Pro Headphones,” or “Pro Max Charger” — but not “pro camera” in lowercase, because unlike LIKE, GLOB is case-sensitive by default. I’ll come back to that distinction in detail shortly, because it’s the single biggest thing that trips people up when switching between the two operators.
GLOB Wildcard Characters
GLOB supports a different set of wildcard characters than LIKE. Here’s the full rundown:
*matches any sequence of zero or more characters (equivalent to%in LIKE)?matches exactly one character (equivalent to_in LIKE)[...]matches any single character within the specified set or range[^...]matches any single character NOT within the specified set or range
Let’s go through each of these with examples.
The Asterisk Wildcard (*)
The asterisk matches any number of characters, including zero.
-- Names starting with "A"
SELECT name FROM customers WHERE name GLOB 'A*';
-- Names ending with "son"
SELECT name FROM customers WHERE name GLOB '*son';
-- Names containing "ann" anywhere
SELECT name FROM customers WHERE name GLOB '*ann*';
The Question Mark Wildcard (?)
The question mark matches exactly one character, no more and no less.
-- 4-letter names starting with "J" and ending with "n"
SELECT name FROM customers WHERE name GLOB 'J??n';
This would match “John” but not “Jon” (too short) or “Jordan” (too long), because each ? must correspond to exactly one character.
Character Classes ([...])
Square brackets let you define a set of acceptable characters for a single position in the pattern.
-- Product codes starting with A, B, or C followed by any 3 characters
SELECT product_code FROM products WHERE product_code GLOB '[ABC]???';
You can also use ranges inside brackets:
-- Names starting with any letter from A to M
SELECT name FROM customers WHERE name GLOB '[A-M]*';
And you can negate a character class using a caret at the start:
-- Names NOT starting with a vowel
SELECT name FROM customers WHERE name GLOB '[^AEIOU]*';
This flexibility with character classes is something LIKE simply doesn’t offer at all — it’s one of GLOB’s biggest advantages for more precise pattern matching.
GLOB Is Case-Sensitive — This Is the Big One
I want to dedicate a full section to this because it’s the most important practical difference between GLOB and LIKE, and it catches nearly everyone off guard at least once.
SELECT name FROM customers WHERE name GLOB 'j*';
This query will NOT match “John,” “Jane,” or “Jordan,” because the pattern j* has a lowercase “j,” and GLOB performs a byte-for-byte case-sensitive comparison. If you wanted to match those names, you’d need to write the pattern with an uppercase “J”:
SELECT name FROM customers WHERE name GLOB 'J*';
Compare this to LIKE, which is case-insensitive for ASCII characters by default:
SELECT name FROM customers WHERE name LIKE 'j%';
This LIKE query WILL match “John,” “Jane,” and “Jordan” despite the lowercase “j” in the pattern, because LIKE doesn’t care about case (at least for standard ASCII letters).
This distinction is exactly why I reach for GLOB whenever I specifically need case-sensitive filtering — for example, matching exact-case product SKUs, case-sensitive file paths, or distinguishing between differently-cased abbreviations that mean different things in my data (like “ID” vs “id” as different column conventions in imported data).
GLOB vs. LIKE: Side-by-Side Comparison
Here’s a table comparing the two operators directly, since I find this is the fastest way to internalize the differences:
| Feature | GLOB | LIKE |
|---|---|---|
| Case sensitivity | Case-sensitive | Case-insensitive (ASCII) |
| Multi-character wildcard | * | % |
| Single-character wildcard | ? | _ |
Character classes ([abc], [a-z]) | Supported | Not supported |
Negated character class ([^abc]) | Supported | Not supported |
| Escape character support | Not built-in | Supported via ESCAPE clause |
| Index usage | Can use index for left-anchored patterns | Can use index for left-anchored patterns |
That last row deserves a note: both GLOB and LIKE can potentially use an index if the pattern starts with a fixed prefix (like 'Pro*' or 'Pro%') rather than starting with a wildcard (like '*Pro' or '%Pro'). If your pattern starts with a wildcard, SQLite has no choice but to scan every row, since there’s no way to know from an index where in the string the match might begin.
Negating GLOB
Just like with LIKE, you can negate a GLOB match using NOT:
SELECT name FROM customers
WHERE name NOT GLOB 'A*';
This returns every customer whose name does not start with an uppercase “A.”
Practical Real-World Examples
Let me walk through a few scenarios where I’ve genuinely used GLOB in real projects, since abstract syntax only gets you so far.
Filtering File Paths
If you’re storing file paths in a SQLite database (a common pattern for local desktop apps or media libraries), GLOB’s Unix-shell-style syntax feels very natural:
-- Find all .jpg files
SELECT file_path FROM media WHERE file_path GLOB '*.jpg';
-- Find files in a specific directory
SELECT file_path FROM media WHERE file_path GLOB '/home/user/photos/*';
Validating Structured Codes
Suppose you have product SKUs that always follow a strict format: one uppercase letter, followed by exactly four digits.
SELECT sku FROM products
WHERE sku GLOB '[A-Z][0-9][0-9][0-9][0-9]';
This is a clean way to spot malformed SKUs that don’t match your expected format — genuinely useful for a quick data-quality check without writing a full regular expression engine into your app.
Case-Sensitive Exact Matching Scenarios
Suppose your application imports data from multiple sources, and some rows have status codes recorded as “Active” while others (due to a bug in an older import script) were recorded as “active” or “ACTIVE.” If you specifically want to isolate the ones that don’t match your canonical capitalization:
SELECT * FROM records
WHERE status GLOB 'Active'
OR status GLOB 'ACTIVE';
I’ve used this exact kind of query to audit and clean up inconsistent legacy data.
GLOB and Indexes
SQLite can use an index to speed up a GLOB query, but only under specific conditions: the pattern must not start with a wildcard character. If your pattern is 'Pro*', SQLite can use a B-tree index on that column to jump directly to rows starting with “Pro,” then scan forward only as far as needed. But a pattern like '*Pro' or '*Pro*' forces a full table scan, because there’s no indexed way to search for a substring appearing anywhere in the text.
You can verify this behavior yourself using EXPLAIN QUERY PLAN:
CREATE INDEX idx_products_name ON products(product_name);
EXPLAIN QUERY PLAN
SELECT * FROM products WHERE product_name GLOB 'Pro*';
If the pattern is left-anchored (starts with a literal prefix), you should see the index being used in the query plan output.
Escaping Special Characters in GLOB
Unlike LIKE, GLOB doesn’t have a built-in ESCAPE clause for treating wildcard characters as literals. If you need to search for a literal asterisk or question mark in your data, you have to wrap it in a character class instead, since character classes only match a single literal character (or set of characters) rather than acting as wildcards themselves.
-- Search for a literal asterisk character in the text
SELECT text FROM notes WHERE text GLOB '*[*]*';
Here, [*] inside the pattern means “match this single literal asterisk character,” rather than “match zero or more of anything.” This is a bit of a workaround, but it’s the standard technique for escaping wildcards in GLOB patterns.
When to Use GLOB vs. LIKE
Here’s my personal rule of thumb, built from years of using both:
- Use LIKE when you want simple, case-insensitive substring or prefix matching, especially for user-facing search features where users won’t be typing exact-case queries.
- Use GLOB when case sensitivity matters, when you need more precise character-class matching (like validating formats), or when you’re working with data that has Unix-style path or filename conventions.
- If you need genuinely complex pattern matching — things like alternation (
cat|dog), repetition counts, or lookaheads — neither GLOB nor LIKE will get you there. At that point, you’d want SQLite’s REGEXP operator, which requires either compiling in regex support or defining a custom REGEXP function via the application layer, since SQLite doesn’t include regex support out of the box.
Common Mistakes to Avoid
- Assuming GLOB is case-insensitive like LIKE. It isn’t, and this is far and away the most common source of confusion.
- Using
%and_in a GLOB pattern, expecting them to act as wildcards. They don’t — GLOB only recognizes*,?, and[...]. A%in a GLOB pattern is treated as a literal percent sign character. - Forgetting that GLOB patterns starting with a wildcard prevent index usage, leading to unexpectedly slow queries on large tables.
- Trying to use an ESCAPE clause with GLOB. It’s not supported — use character-class escaping instead.
Best Practices
- Default to LIKE for general-purpose, case-insensitive text search unless you have a specific reason to need case sensitivity.
- Reach for GLOB when working with structured codes, file paths, or any scenario where distinguishing between uppercase and lowercase actually matters.
- Anchor your GLOB patterns with a literal prefix whenever possible to allow SQLite to use an index.
- Use character classes (
[A-Z],[0-9],[^...]) to validate structured data formats cleanly, rather than writing multiple OR conditions. - Test your GLOB patterns against real sample data before deploying them in production queries, since the case-sensitivity behavior can produce silently incorrect (empty) results if you’re not careful about matching the exact casing of your pattern.
GLOB might not be the first pattern-matching tool people reach for in SQLite, but once you understand its case-sensitive, shell-style behavior and its support for character classes, it becomes an incredibly precise tool for exactly the kinds of structured, format-sensitive matching that LIKE simply can’t handle on its own.
Frequently Asked Questions
Can I use GLOB with numbers, not just text? GLOB operates on text values. If you apply it to a numeric column, SQLite will implicitly convert the number to its text representation before matching, based on its type affinity rules. This generally works fine for simple cases, but I prefer to be explicit and wrap numeric columns with CAST(column AS TEXT) when using GLOB against them, just to avoid any ambiguity about how the conversion is happening.
SELECT * FROM products WHERE CAST(sku_number AS TEXT) GLOB '4???';
Does GLOB support alternation, like matching “cat” or “dog”? No — GLOB’s wildcard syntax doesn’t include an alternation operator. If you need to match one of several complete alternatives, you’ll need to combine multiple GLOB conditions with OR, or switch to the REGEXP operator (which requires additional setup, since SQLite doesn’t include regex support by default).
SELECT * FROM pets WHERE name GLOB 'cat*' OR name GLOB 'dog*';
Is GLOB standard SQL, or specific to SQLite? GLOB is not part of the ANSI SQL standard — it’s a SQLite-specific operator, inspired by Unix shell globbing syntax. If you’re writing SQL that needs to be portable across different database systems (PostgreSQL, MySQL, SQL Server), GLOB won’t be available, and you’d need to rely on LIKE (which is standard) or database-specific regex functions instead.
Can GLOB patterns be stored in a column and used dynamically? Yes. You can bind a GLOB pattern as a parameter, and even store patterns in a table for dynamic, configuration-driven filtering.
SELECT * FROM files WHERE file_path GLOB ?;
This is useful for building configurable file-filtering or validation rules without hardcoding every possible pattern into your SQL.
GLOB in Real Applications: A Closer Look at File Management Tools
I mentioned earlier that GLOB feels natural for file path filtering, and I want to expand on that because it’s genuinely one of the best real-world use cases for this operator. If you’re building a local-first application that indexes files on a user’s disk — a media library manager, a document search tool, a backup utility — GLOB’s Unix-shell-style syntax maps almost one-to-one onto the kind of pattern matching users already intuitively understand from typing wildcard patterns into a terminal or file browser.
-- Find all images regardless of common extension
SELECT file_path FROM files
WHERE file_path GLOB '*.jpg' OR file_path GLOB '*.png' OR file_path GLOB '*.gif';
-- Find all files inside a specific subdirectory tree
SELECT file_path FROM files
WHERE file_path GLOB '/Users/me/Documents/Projects/*';
-- Find backup files matching a common naming convention
SELECT file_path FROM files
WHERE file_path GLOB '*.bak' OR file_path GLOB '*~';
Combining GLOB with Other SQLite Features
GLOB composes cleanly with other clauses you’d expect — GROUP BY, aggregate functions, JOINs, and subqueries all work exactly as you’d anticipate.
SELECT
CASE
WHEN file_path GLOB '*.jpg' THEN 'Image'
WHEN file_path GLOB '*.mp4' THEN 'Video'
WHEN file_path GLOB '*.pdf' THEN 'Document'
ELSE 'Other'
END AS file_type,
COUNT(*) AS file_count
FROM files
GROUP BY file_type;
This is a pattern I use often when building quick data-summary reports over unstructured file listings — bucketing files by extension using GLOB inside a CASE expression, then aggregating the buckets with GROUP BY and COUNT.
Testing and Debugging GLOB Patterns
Because GLOB’s case sensitivity can silently produce zero results rather than an error, I always test new GLOB patterns against a small, known sample before trusting them in a larger query. A quick sanity check I run constantly:
SELECT 'TestValue123' GLOB 'Test*'; -- returns 1 (true)
SELECT 'TestValue123' GLOB 'test*'; -- returns 0 (false) — case mismatch
Running these standalone GLOB expressions directly (without a FROM clause) against sample strings is a fast way to confirm a pattern behaves the way you expect before embedding it inside a larger query against real data.
GLOB Performance Notes for Large Datasets
For genuinely large tables, remember that a leading wildcard ('*text' or '*text*') forces SQLite to scan every row, since no index can help locate a substring appearing at an arbitrary position within the text. If you find yourself needing frequent substring searches on a large table, and a leading-wildcard GLOB or LIKE query is becoming a performance bottleneck, it’s worth investigating SQLite’s FTS5 (Full-Text Search) extension instead, which builds a proper inverted index specifically designed for fast substring and token-based text search — something GLOB, by its very nature as a simple pattern-matcher, was never designed to optimize for.
