The SQLite like() function enables you to compare a string against a given pattern. You can also specify an escape character if required.

You can use the like() function as an alternative to the LIKE operator (which enables you to run the Y LIKE X [ESCAPE Z] expression).

Syntax

You can use either of the following:

like(X,Y)
like(X,Y,Z)

• X is the pattern
• Y is the string
• Z is an optional escape character

Basic Example

Take the following table:

CatId       CatName   
----------  ----------
1           Brush     
2           Flutter   
3           100%Fluff 
4           100$Fluff 

We can run the following code to return all rows where the cat’s name starts with the letter F:

SELECT * FROM Cats 
WHERE like('F%', CatName);

Result:

CatId       CatName   
----------  ----------
3           Flutter   

In this example, I used the % wildcard character to indicate any number of characters could follow (including zero characters), and that they can be any character.

The Underscore (_) Character

Here’s an example that uses the underscore (_) character. This wildcard character matches exactly one character – no more, and no less.

SELECT * FROM Cats 
WHERE like('Br_sh', CatName);

Result:

CatId       CatName   
----------  ----------
1           Brush     

As mentioned, it only matches a single character. So, based on my data, I can’t do the following and expect any matches.

SELECT * FROM Cats 
WHERE like('Br_', CatName);

Result:

(This is intentionally blank due to no results).

Escaping Characters

You can also escape characters if required. To do this, add the escape character as a third parameter (and also add it prior to the character that you want to escape).

SELECT * FROM Cats 
WHERE like('100\%F%', CatName, '\');

Result:

CatId       CatName   
----------  ----------
3           100%Fluff 

In this case I escaped the percentage sign (%). I did this because I was looking for cats that had an actual percentage sign within their names. Actually, in this case I was searching for a very specific cat name – one that begins with 100%F.

If I hadn’t escaped that, it would’ve been used as a wildcard, and the results would have been different.

Here’s what I get when I remove the escape character.

SELECT * FROM Cats 
WHERE like('100%F%', CatName);

Result:

CatId       CatName   
----------  ----------
3           100%Fluff 
4           100$Fluff 

Return a Boolean Value

You can use the like() function to return a 0 or 1, depending on whether there’s a match or not.

This can be handy if you need to return all rows, but also with a flag that shows whether or not they match your criteria.

Here’s an example.

SELECT 
  CatName,
  like('F%', CatName) 
FROM Cats;

Result:

CatName     CatName LIKE 'F%'
----------  ------------------
Brush       0                 
Flutter     1                 
100%Fluff   0                 
100$Fluff   0                 

In this case, only one row matched the criteria, but we still got to see all the other rows.

Case-Sensitivity

By default, the SQLite like() function 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. That article is written for the LIKE operator, but it also applies to the like() function.