SQLite is a widely used, embedded open-source database engine. It powers many apps and websites we use every day. A key feature of any database system is the ability to search and filter data efficiently. This is where the GLOB operator in SQLite comes in handy.
Let’s first create a sample table called
employees to demonstrate how the SQLite GLOB clause works:
CREATE TABLE employees ( id INTEGER PRIMARY KEY, name TEXT, department TEXT ); INSERT INTO employees VALUES (1, 'John Doe', 'Engineering'), (2, 'Jane Smith', 'Sales'), (3, 'Bob Williams', 'Engineering'), (4, 'Sara Johnson', 'Marketing');
This table has sample employee data with an id, name, and department for each employee.
What's in this article
What is SQLite GLOB?
The GLOB operator in SQLite is used to match text values against a pattern. It is similar to the SQLite LIKE operator but uses powerful unix-style wildcards for more flexible pattern matching.
The basic syntax is:
column GLOB pattern
This returns rows where the text column matches the specified glob pattern.
How Does SQLite GLOB Work?
The SQLite GLOB clause uses the following special wildcard characters:
*– Matches any sequence of zero or more characters
?– Matches exactly one character
SELECT * FROM employees WHERE name GLOB 'J*';
This will match all names starting with ‘J’, returning John Doe’s record.
? wildcard will match any single character:
SELECT * FROM employees WHERE name GLOB 'B?b';
This will match Bob, returning Bob Williams.
We can combine multiple wildcards in any order to create complex glob patterns for powerful matching capabilities.
SQLite GLOB Syntax and Parameters
The syntax for the SQLite GLOB clause is straightforward:
column_name GLOB pattern
column_nameis the name of the column to match against
patternis the glob pattern with wildcards to use for matching
The GLOB keyword is followed by the glob pattern to match, enclosed in single quotes.
The pattern supports the standard wildcards
? to match strings. The column value is case-sensitive by default.
Practical Examples of SQLite GLOB Usage
Let’s look at some practical examples of using SQLite GLOB to match various patterns in text data using the WHERE clause.
Match any names starting with ‘J’:
SELECT * FROM employees WHERE name GLOB 'J*';
Find names ending with ‘ohn’:
SELECT * FROM employees WHERE name GLOB '*ohn';
Match ‘Smith’ or ‘Johnson’:
SELECT * FROM employees WHERE name GLOB 'Smith' OR name GLOB 'Johnson';
Match 5 letter names starting with ‘S’ and ending in ‘h’:
SELECT * FROM employees WHERE name GLOB 'S???h';
Comparison of SQLite GLOB and LIKE
Both GLOB and LIKE offer pattern matching in SQLite, but there are some key differences:
- GLOB is case-sensitive by default, LIKE is not
- GLOB uses powerful
?wildcards, LIKE only uses
- GLOB matches the entire string, LIKE matches substrings
- GLOB has no escape character, while LIKE uses
\to escape special characters
For simple substring matching, LIKE may be faster. But GLOB offers greater flexibility for accurately matching entire strings against patterns.
Common Use Cases for SQLite GLOB
Here are some common use cases where SQLite GLOB shines:
- Matching names/addresses from incomplete user inputs
- Validating codes/IDs with standardized formats
- Filtering log or text data by patterns
- Blocking specific patterns like swear words or spam
For optimal performance, queries should use an index on the GLOB column.
Limitations to Keep in Mind
While GLOB is versatile, some limitations to note:
- It is case-sensitive, so ‘Cat’ won’t match ‘cat’
- SQLite does not support accent/diacritic insensitive GLOB matching
- Complex GLOB patterns can slow down query performance
Frequently Asked Questions
Here are some common questions about SQLite’s GLOB operator:
What is glob in SQL?
GLOB is a pattern matching operator used in SQL databases like SQLite. It lets you match text columns against powerful wildcard patterns.
What’s the difference between LIKE and GLOB in SQLite?
GLOB uses Unix shell-style wildcards while LIKE has SQL-style wildcards. GLOB does full string matches while LIKE matches substrings. GLOB is also case-sensitive.
How do you perform a not equal to match in SQLite?
We can use the
NOT GLOB or
NOT LIKE operators to match text that does not match a pattern.
What does the cast function do in SQLite?
CAST converts a value of one type to another. For example, CAST(column AS TEXT) converts a column to a text string.
The SQLite GLOB operator provides robust pattern-matching capabilities using wildcard characters. It can match entire strings efficiently against complex glob patterns. Combining GLOB with AND/OR conditions and other SQLite features allows us to implement powerful search and filter logic.
The usage is straightforward – specify the GLOB keyword, pattern, and column to match against. With a grasp of how the
? wildcards work, you can leverage SQLite GLOB to solve many real-world string matching problems.