Francisc Francisc - 7 months ago 14
SQL Question

SQLite Like % and _

I can't figure out what the underscore character does in an SQLite

like
statement.
The wildcard character,
%
, is probably the same as in most other SQL databases.

So, what does the damn
_
character do?

Answer

The underscore is also the same as in most other SQL databases and matches any single character (i.e. it is the same as . in a regular expression). From the fine manual:

An underscore ("_") in the LIKE pattern matches any single character in the string.

For example:

-- The '_' matches the single 'c'
sqlite> select 'pancakes' like 'pan_akes';
1
-- This would need '__' to match the 'ca', only one '_' fails.
sqlite> select 'pancakes' like 'pan_kes';
0
-- '___' also fails, one too many '_'.
sqlite> select 'pancakes' like 'pan___kes';
0

And just to make sure the results make sense: SQLite uses zero and one for booleans.