SQLite GLOB

SQLite’s GLOB operator is a pattern matching operator used in SQL queries. It is similar to the LIKE operator, but it uses a different syntax for specifying patterns. The GLOB operator allows for more flexible and powerful pattern matching capabilities than the LIKE operator.

Syntax

The syntax for the GLOB operator is as follows:

expr GLOB pattern

Here, expr is the expression to be matched against the pattern. The pattern is a string that may contain special characters that represent wildcard characters. These special characters are:

* Matches any sequence of zero or more characters.
? Matches any single character.
[set] Matches any single character in the specified set of characters. The set may include ranges, such as [a-z] or [0-9].

Examples

For example, to match any string that starts with “foo” and ends with “bar”, you could use the following query:

SELECT * 
FROM mytable 
WHERE mycolumn GLOB 'foo*bar';

This would match strings such as “foobar”, “foobazbar”, and “foo123bar”.

The GLOB operator is case sensitive by default, but you can use the COLLATE keyword to specify a case-insensitive collation. For example:

SELECT * 
FROM mytable 
WHERE mycolumn GLOB 'foo*bar' 
COLLATE nocase;

This would match strings such as “foobar”, “FoobazBar”, and “fOo123BaR”.

In addition to the wildcard characters, the GLOB operator also supports the backslash character as an escape character. This allows you to match literal instances of the wildcard characters, or to escape other special characters. For example:

SELECT * 
FROM mytable 
WHERE mycolumn GLOB 'foo\*bar';

This would match strings such as “foo*bar”, but not “foobar” or “foobazbar”.

Overall, the GLOB operator provides a powerful and flexible way to perform pattern matching in SQLite queries. By using wildcard characters and the backslash escape character, you can match a wide variety of string patterns with precision and control.