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.
SELECT name FROM products WHERE name GLOB '[A-C]*';
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
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]') |
-- 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. GLOBandLIKEuse different wildcards.%and_are ordinary characters in aGLOBpattern, and*and?are ordinary characters in aLIKEpattern.- 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 defaultBINARYcollation, becauseGLOBis case sensitive. A leading*scans the table. - Portability: code that uses
GLOBonly runs on SQLite. PreferLIKEwhen 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 SQLiteCHECKconstraints, but the empty string also passes, so testx <> ''too.