SQLite, a relational database management system, offers the LIKE operator for pattern matching in queries. It's an essential tool for flexible, dynamic searches, used widely in web and mobile applications. In this guide, we'll explore SQLite's LIKE operator, its syntax, and various use cases.
Before delving into the specifics, let's understand that SQLite's LIKE operator is case-sensitive and not case-preserving. This means it considers 'A' and 'a' as different characters, but it doesn't preserve the case of matched characters. For case-insensitive searches, you can use the GLOB operator or concatenate the lower() function with LIKE (discussed later).
Basic Syntax and Usage
The basic syntax for using the LIKE operator is straightforward. It's comprised of a pattern string that defines what you're searching for, and a column value from your table.
Here's the general syntax:
<SELECT> FROM <TABLE> WHERE <COLUMN> LIKE <PATTERN>
The pattern string can include literal text and wildcard characters, represented by '_' (underscore) and '%' (percent sign). The underscore matches any single character, while the percent sign matches any sequence of characters, including zero.
Underscore (_) - Single Character Wildcard
The underscore (_) acts as a wildcard character, matching any single character. For instance, 'J_o' matches 'Joe' or 'John' but not 'Johnsson' (too many characters).
Example:
SELECT * FROM users WHERE first_name LIKE 'J_o';
Percent Sign (%) - Sequence Wildcard
The percent sign (%) is a wildcard that matches any sequence of characters, including zero. So, '%o' matches 'Joe', 'John', 'London', etc.
Example:
SELECT * FROM users WHERE first_name LIKE '%o';
Advanced LIKE Patterns
Sometimes, you need more flexibility. Here are advanced LIKE patterns using CONCAT, OR, and IN:
The CONCAT function allows you to combine multiple LIKE conditions and eliminate false positives. The OR operator can match any of multiple patterns, while the IN clause matches any pattern in a list.
CONCAT for Combining LIKE Conditions
When searching for a name like 'John' or 'Jonathan', using a simple LIKE condition ('%ohn%') can also match 'Johnson', 'Johndoe', etc. To eliminate these false positives, use CONCAT:
Example:
SELECT * FROM users WHERE first_name LIKE CONCAT('J%h%') OR first_name LIKE CONCAT('John%');
OR for Matching Multiple Patterns
The OR operator can match any of multiple patterns. Be cautious with its performance, though, as it may scan the entire index (if any) for each pattern.
Example:
SELECT * FROM users WHERE first_name LIKE '%o%' OR first_name LIKE '%n%';
IN for Matching Values from a List
The IN clause can match any pattern in a list, making it a cleaner alternative to OR in some cases.
Example:
SELECT * FROM users WHERE first_name LIKE ANY ('%o%', '%n', '%o%');
For a more intuitive approach, consider using GLOB patterns or the FTS (Full-Text Search) module available from SQLite version 3.6.0.
Case-Insensitive Searches
As mentioned initially, LIKE is case-sensitive. For case-insensitive searches, you can use either GLOB patterns or concatenate the lower() function with LIKE:
GLOB for Case-Insensitive Patterns
GLOB patterns are more powerful and intuitive than LIKE, offering case-insensitive searches out-of-the-box. Version 3.31.0 and later also supports the IGNORE CASE clause with GLOB.
Example:
SELECT * FROM users GLOB 'first_name:*john*';
lower() Function for Case-Insensitive LIKE
If you're stuck with an older version of SQLite or prefer to stick with LIKE, you can concatenate the lower() function to make searches case-insensitive:
Example:
SELECT * FROM users WHERE lower(first_name) LIKE lower('J%h%');
While this approach works, it's slower and relies on the assumption that string case conversion is quick and deterministic.
Finally, consider other alternatives like the FTS module, which supports full-text indexing, ranking, highlight, and spellcheck capabilities. For complex, high-performance search needs, it might be worth investigating.