SQLite - GLOB Clause

SQLite - Pattern Matching with the GLOB Operator

The GLOB operator in SQLite is used in a WHERE clause to match text values against a pattern. It is similar to the LIKE operator but uses Unix-style wildcards and is always case-sensitive.

GLOB Wildcards

GLOB supports three types of wildcards:

• The asterisk (*****): Matches any sequence of zero or more characters.
• The question mark (?): Matches any single character.
• Character lists ([…]): Matches any single character that appears within the brackets. You can also use a caret (^) to invert the match, e.g., [^abc] matches any character except ‘a’, ‘b’, or ‘c’.

Syntax

SELECT column_list
FROM table_name
WHERE column_name GLOB 'pattern';

GLOB vs. LIKE

Here’s a quick comparison to help you choose the right operator:

Feature	GLOB	LIKE
Case Sensitivity	Always Case-Sensitive	Case-Insensitive by default (for ASCII). Can be made sensitive with PRAGMA case_sensitive_like = ON;.
Multi-Character Wildcard	***** (asterisk)	% (percent)
Single-Character Wildcard	? (question mark)	_ (underscore)
Character List	[…] and [^…]	Not supported
Common Use Case	File path patterns, strict matching	General-purpose text searching, SQL standard

Examples

Using our COMPANY table, let’s explore some GLOB queries.

Example 1: Find names starting with ‘P’ or ‘T’.

sqlite> SELECT NAME FROM COMPANY WHERE NAME GLOB '[PT]*';

This will produce the following result:

NAME
----------
Paul
Teddy

Example 2: Find all names that are exactly 4 characters long and end with ‘m’.

sqlite> SELECT NAME FROM COMPANY WHERE NAME GLOB '???m';

This will produce the following result:

NAME
----------
Kim

Example 3: Find addresses containing a hyphen.

sqlite> SELECT ADDRESS FROM COMPANY WHERE ADDRESS GLOB '*-*';

This will produce the following result:

ADDRESS
----------
Rich-Mond
South-Hall