Search code examples
sqlsqlitesql-like

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

Solution

  • 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.