How to Make SQLite’s LIKE Operator Case-Sensitive

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.

However, there is a technique you can use to make it case-sensitive.

SQLite has a PRAGMA statement called case_sensitive_like, which is designed to specifically make the LIKE operator case-sensitive for ASCII characters.

The reason I specify “ASCII characters” is because, the LIKE operator is case sensitive by default for unicode characters that are beyond the ASCII range.

Therefore, if you need the LIKE operator to be case sensitive in the ASCII range, the case_sensitive_like PRAGMA statement could be what you’re looking for.

This also applies to the like() function, which works exactly the same as the LIKE operator.

Enabling/Disabling Case-Sensitivity

You can use any of the following boolean values to enable case-sensitivity:

1
on
true
yes

You can use any of the following boolean values to disable case-sensitivity:

0
off
false
no

The argument may be either in parentheses or it may be separated from the pragma name by an equal sign.

Keyword arguments can optionally appear in quotes.

Case-Insensitive Example

First let’s see what happens when we don’t use the case_sensitive_like PRAGMA statement.

SELECT 
  'a' LIKE 'A',
  'æ' LIKE 'Æ';

Result:

'a' LIKE 'A'  'æ' LIKE 'Æ'
------------  ------------
1             0

In this case, the first column is case-insensitive – the uppercase A matches the lowercase a.

And here’s a query against a database table.

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, we get a match against all cats whose names start with Br, even though our criteria used a lowercase b.

Case-Sensitive Example

Now let’s see what happens when we use the case_sensitive_like PRAGMA statement to enable case-sensitivity in the ASCII range.

PRAGMA case_sensitive_like = 1;
SELECT 
  'a' LIKE 'A',
  'æ' LIKE 'Æ';

Result:

'a' LIKE 'A'  'æ' LIKE 'Æ'
------------  ------------
0             0

So this time the uppercase A does not match the lowercase a.

And here’s the database example after enabling case-sensitivity.

SELECT 
  CatName,
  CatName LIKE 'br%'
FROM Cats;

Result:

CatName     CatName LIKE 'br%'
----------  ------------------
Brush       0                 
Brash       0                 
Broosh      0                 
100%Fluff   0                 
100$Fluff   0

Notice that I didn’t need to run the PRAGMA statement again. The setting remains in place for my connection until I change it.

Disable Case-Sensitivity

Here’s an example of disabling case-sensitivity and running the query again.

PRAGMA case_sensitive_like = 0;
SELECT 
  CatName,
  CatName LIKE 'br%'
FROM Cats;

Result:

CatName     CatName LIKE 'br%'
----------  ------------------
Brush       1                 
Brash       1                 
Broosh      1                 
100%Fluff   0                 
100$Fluff   0

The Like() Function

As mentioned, the case_sensitive_like PRAGMA statement also affects the like() function.

PRAGMA case_sensitive_like = 1;
SELECT 
  CatName,
  like('Br%', CatName),
  like('br%', CatName)
FROM Cats;

Result:

CatName     like('Br%', CatName)  like('br%', CatName)
----------  --------------------  --------------------
Brush       1                     0                   
Brash       1                     0                   
Broosh      1                     0                   
100%Fluff   0                     0                   
100$Fluff   0                     0

Unicode-Aware LIKE Operator

You can also use the SQLite ICU extension if you need a unicode aware LIKE operator.

The implementation of LIKE included in this extension uses the ICU function u_foldCase() to provide case independent comparisons for the full range of unicode characters.