SQLite Like % and _

sql sqlite sql-like
Francisc picture Francisc · Sep 6, 2011 · Viewed 96.7k times · Source

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

Benoit picture Benoit · Sep 6, 2011

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

Related questions

SQLite query, 'LIKE'

I am trying to retrieve information from my database. I have words like Lim Won Mong and Limited. If I do my query, SELECT name FROM memberdb WHERE name LIKE '%LIM%', it displays both Lim Won Mong and …

Read More
Match only entire words with LIKE?

So 'awesome document' LIKE '%doc%' is true, because doc is a sub-string. But, I want it to be false while 'awesome doc' or 'doc awesome' or 'awesome doc awesome' should be true. How can I do with with …

Read More
Return rows where first character is non-alpha

I'm trying to retrieve all columns that start with any non alpha characters in SQlite but can't seem to get it working. I've currently got this code, but it returns every row: SELECT * FROM TestTable WHERE TestNames NOT LIKE '[…

Read More