I'm an Android developer and recently came across the GLOB clause in SQLite. I cannot understand why we need GLOB given that LIKE is already in place.
Both clauses have wildcards to represent single and multiple characters. The only difference is that GLOB is case sensitive.
But is that all? Are there any queries where LIKE is a bad or inappropriate choice? Are there any cases where we absolutely have to use GLOBE vs LIKE or vice versa?
Case sensitivity is useful by itself, because this works better with normal indexes.
Additionally, GLOB supports character classes:
Globbing rules:
*Matches any sequence of zero or more characters.
?Matches exactly one character.
[...]Matches one character from the enclosed list of characters.
[^...]Matches one character not in the enclosed list.With the
[...]and[^...]matching, a]character can be included in the list by making it the first character after[or^. A range of characters can be specified using-. Example:[a-z]matches any single lower-case letter. To match a-, make it the last character in the list.
GLOB will use indexes in a lot of cases where like will not, with small amounts of data this difference will be insignificant but with large amounts of data, this can have a dramatic impact on performance.
Seeking data (using indexes) is much faster than table scans, table scans process all the rows in a table to work out what meets the criteria, seeks use the indexes to find the data.
So take the following example (where an index has been created for the lastname column)
select * from table1 where lastname like 'brown'
and
select * from table1 where lastname glob 'brown'
the first example does a table scan (index not used), the second example uses the index.
where glob really comes into its own is replacing characters in search criteria, so glob 'brow?' still uses an index, so will glob 'brow%', a table scan is used for the equivalent like command.
