SQLite | Querying Data | Pattern Matching with GLOB
GLOB performs case-sensitive pattern matching with Unix-style wildcard characters.
SELECT columns FROM table_name WHERE column GLOB pattern;
| Pattern | Meaning |
|---|---|
* |
Zero or more arbitrary characters |
? |
Exactly one arbitrary character |
[abc] |
One character from the set |
[a-d] |
One character from the range |
[^abc] |
One character not in the set |
Thus a*b matches ab, axb, and aoneb; a?b matches axb but not ab; [mk]* matches strings beginning with m or k; and [a-zA-Z0-9]* matches strings beginning with an ASCII letter or digit.
create table user (id integer, screenname text);
insert into user values (1, 'Blogger');
insert into user values (2, 'rondobell');
insert into user values (3, 'blue*star');
insert into user values (4, '2525');
insert into user values (5, 'Star');
select * from user where screenname glob 'b*';
The last query returns only blue*star. Since GLOB is case-sensitive, use a character set to match both cases:
select * from user where screenname glob '[bB]*';
select * from user where screenname glob '[a-zA-Z]*';
Escape special characters
To match *, ?, [ or ] literally, put the character in brackets. The pattern *** simply matches arbitrary strings, whereas this query finds a literal asterisk:
select * from user where screenname glob '*[*]*';
It returns blue*star.