Skip to content
Tutorials Center
My learning

Topics

SQL Reference ยท Operators and Predicates

SQL GLOB operator

Reference for the SQLite GLOB operator: Unix-style wildcards *, ?, [abc] and [^abc], case-sensitive matching, differences from LIKE, and alternatives elsewhere.

GLOB is a SQLite operator that matches text against a Unix-shell-style pattern. Unlike LIKE, it is always case sensitive, and it supports character classes such as [0-9]. It is not part of standard SQL: PostgreSQL, MySQL, SQL Server and Oracle have no GLOB operator.

The wildcards are * (any run of characters, including none) and ? (exactly one character). Square brackets match one character from a set or range.

SQL
SELECT name FROM products WHERE name GLOB '[A-C]*';

Try it

SQL GLOB wildcards and character classes ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

SQL GLOB is case sensitive, LIKE is not

Press Run (or Ctrl + Enter) to see the result here.

Syntax

SQL
expression [NOT] GLOB pattern
Pattern element Meaning
* Zero or more of any character.
? Exactly one character.
[abc] One character from the set.
[a-z] One character in the range.
[^abc] One character not in the set.

Returns: 1 (true), 0 (false), or NULL if either operand is NULL.

To match a literal *, ? or [, put it inside brackets: [*], [?], [[]. There is no ESCAPE clause.

Dialect differences

Need SQLite PostgreSQL MySQL SQL Server Oracle
Case-sensitive pattern GLOB LIKE LIKE BINARY or a binary collation LIKE with a _CS collation LIKE
Character classes GLOB '[0-9]*' ~ '^[0-9]' or SIMILAR TO REGEXP '^[0-9]' LIKE '[0-9]%' REGEXP_LIKE(x, '^[0-9]')
SQL
-- PostgreSQL: starts with a digit
SELECT name FROM products WHERE name ~ '^[0-9]';

-- SQL Server: starts with a digit
SELECT name FROM products WHERE name LIKE '[0-9]%';

Usage notes

  • Case matters. 'Apple' GLOB 'a*' is false. Use a class such as [Aa]* when you want both.
  • GLOB and LIKE use different wildcards. % and _ are ordinary characters in a GLOB pattern, and * and ? are ordinary characters in a LIKE pattern.
  • Not a regular expression. There are no repetition operators, groups or alternation. [0-9]* means "one digit followed by anything", not "any number of digits".
  • Indexes: a pattern with a literal prefix ('Ca*') can use an index on a column with the default BINARY collation, because GLOB is case sensitive. A leading * scans the table.
  • Portability: code that uses GLOB only runs on SQLite. Prefer LIKE when the pattern is simple and the code must move between databases.
  • Validation tricks such as x NOT GLOB '*[^0-9]*' ("contains only digits") are common in SQLite CHECK constraints, but the empty string also passes, so test x <> '' too.