In SQLite, you can use the LIKE operator in your queries to do a pattern matching comparison.
For example, you can add it to your WHERE clause in order to return only rows that match a given pattern.
However, adding it to the WHERE clause isn’t the only way you can use the LIKE operator. You can also use it to return a boolean value.
Basic Example
Here’s a basic example that uses the LIKE operator in a WHERE clause.
SELECT * FROM Artist
WHERE Name LIKE 'Black%';Result:
ArtistId Name ---------- ------------------- 11 Black Label Society 12 Black Sabbath 169 Black Eyed Peas
In this case I wanted to return all artists whose name’s start with Black.
The percentage sign is a wildcard character that matches zero or more of any character (including spaces).
Without the Wildcard
If I hadn’t used the wildcard character, I wouldn’t have gotten any results.
Here’s an example of what I mean:
SELECT * FROM Artist
WHERE Name LIKE 'Black';Result:
(That’s intentionally blank, because there were no results.)
Multiple Wildcards
You can use more than one wildcard character if required. In this example I add another one to the front of my pattern.
SELECT * FROM Artist
WHERE Name LIKE '%Black%';Result:
ArtistId Name ---------- ------------------- 11 Black Label Society 12 Black Sabbath 38 Banda Black Rio 137 The Black Crowes 169 Black Eyed Peas
This results in more rows being returned, because there are two rows that have Black in their name, but just not at the start.
The Underscore (_) Wildcard
You also have the option of using the underscore (_) wildcard.
This wildcard character matches exactly one character (any character).
Consider the following table called Cats:
CatId CatName ---------- ---------- 1 Brush 2 Brash 3 Broosh 4 100%Fluff 5 100$Fluff
Yes, some of those cats have weird names, but they’ll come in handy.
Here’s an example of using the underscore wildcard in a query against that table.
SELECT * FROM Cats
WHERE CatName LIKE 'Br_sh';Result:
CatId CatName ---------- ---------- 1 Brush 2 Brash
Notice that the cat named Broosh isn’t included.
To include Broosh, I would need to use two underscores:
SELECT * FROM Cats
WHERE CatName LIKE 'Br__sh';Result:
CatId CatName ---------- ---------- 3 Broosh
However, this excludes the other two cats.
I could always use a percentage sign to include the other cats.
SELECT * FROM Cats
WHERE CatName LIKE 'Br%sh';Result:
CatId CatName ---------- ---------- 1 Brush 2 Brash 3 Broosh
Although, bear in mind that this would also return names with more than two characters between Br and sh.
The ESCAPE Clause
Sometimes your data might actually include the wildcard characters. In other words, the data contains underscores or percentage signs.
This could be an issue if you’re actually trying to match the underscore or percentage sign as literal character and not as a wildcard.
In my table of cats, you’ll notice that one cat has a percentage sign in its name. Another cat has almost exactly the same name, except that, instead of a percentage sign, it’s a dollar sign.
Here’s the table again:
CatId CatName ---------- ---------- 1 Brush 2 Brash 3 Broosh 4 100%Fluff 5 100$Fluff
If I wanted to return only those cats whose names start with 100%F then I would need to escape the %. If I didn’t escape this, then I would get unwanted results.
Here’s what happens if I run a query without escaping the percentage sign.
SELECT * FROM Cats
WHERE CatName LIKE '100%F';Result:
CatId CatName ---------- ---------- 4 100%Fluff 5 100$Fluff
In this case the percentage sign also matched the dollar sign, because the percentage sign was being treated as a wildcard character.
To ensure that the percentage sign isn’t treated as a wildcard character, we can use the ESCAPE clause.
SELECT * FROM Cats
WHERE CatName LIKE '100\%%F' ESCAPE '\';Result:
CatId CatName ---------- ---------- 4 100%Fluff
Return a Boolean Value
You can use the LIKE operator to return a 0 or 1, depending on whether there’s a match or not.
Here’s an example.
SELECT
CatName,
CatName LIKE 'Br%'
FROM Cats;Result:
CatName CatName LIKE 'Br%' ---------- ------------------ Brush 1 Brash 1 Broosh 1 100%Fluff 0 100$Fluff 0
In this case, the first three rows match the criteria, and the last two rows don’t.
Case-Sensitivity
By default, the SQLite LIKE operator is case-insensitive for ASCII characters. This means it will match uppercase and lowercase characters, regardless of which case you use in your pattern.
You can use the case_sensitive_like PRAGMA statement to perform case-sensitive matches in the ASCII range.
See How to Make SQLite’s LIKE Operator Case-Sensitive for examples.
The Like() Function
An alternative way to do it is to use the like() function.
This function does exactly the same thing as the LIKE operator, except that you need to use a slightly different syntax.
All of the above examples could be rewritten to use the like() function if required.