SQLite Like % and _

Question

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 _ character do?

Answer 1

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%_ or a×bc\d%_ but not abc\d%_ nor a×b×××cd%_.

Additionnally with SQLite you have the GLOB keyword which behaves exactly the same way, except that % becomes * and _ becomes ?.

Answer 2

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.