SQLite Like % and _
It is standard SQL that in LIKE
expressions:
%
matches any sequence of characters, including an empty one. It is equivalent to.*
in a regular expression._
matches a single character. It is equivalent to.
in a regular expression.You can choose a character for escaping
%
,_
and itself itself with:... WHERE expr LIKE 'a_b%c\\d\%\_' ESCAPE '\'
This will match
a×b×××c\d%_
ora×bc\d%_
but notabc\d%_
nora×b×××cd%_
.
Additionnally with SQLite you have the GLOB
keyword which behaves exactly the same way, except that %
becomes *
and _
becomes ?
.
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.
Addendum to @Benoit's answer:
The ESCAPE
applies to the most recent LIKE
expression, not all LIKE
expressions. To escape all you must use ESCAPE
multiple times, such as below.
WHERE foo LIKE '%bar^%%' ESCAPE '^' AND foo LIKE '%baz^_%' ESCAPE '^'
This predicate matches values of foo
which contain bar%, or baz plus any character.