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?

Benoit · Answer 1 · 2016-05-04T11:07:49.133

95

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

edited May 04 '16 at 11:07

answered Sep 06 '11 at 17:02

Benoit

76,634
23
210
236

Thank you, Benoit. I chose the other answer judging by time only. – Francisc Sep 06 '11 at 20:37
This saved my life. I was looking how to escape '%' in SQLite queries. But I had to give '\\' to get it working, not just '\' (I was working on Javscript+WebSQL). Below query worked for me. SELECT * FROM offers WHERE title LIKE '%50\\%%' ESCAPE '\\' (this will return all offers with title having 50%) – hashcoder Mar 01 '14 at 09:07
@Francisc, I think you were fair in the beginning to choose the answer according to the time, but this answer is more clear than the currently accepted answer. It would be a service to future users to make this the accepted answer. – Suragch Apr 29 '16 at 01:28
SQLite's `GLOB` is not exactly the same as `LIKE` but with different symbols. It's always case-sensitive, doesn't allow `ESCAPE`, and also supports character classes and ranges. – Ssswift May 29 '17 at 18:42

mu is too short · Accepted Answer · 2011-09-06T17:06:15.700

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.

score 13 · Answer 3 · answered Oct 27 '15 at 00:58

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.

score 0 · Answer 4 · answered Apr 07 '15 at 14:09

0

For the record, I use in XCode/Objective-C environment, '\' doesn't work. Use anything else instead...

C-style escapes using the backslash character are not supported because they are not standard SQL (https://www.sqlite.org/lang_expr.html)

answered Apr 07 '15 at 14:09

Jan ATAC

1,212
1
18
36

SQLite Like % and _

4 Answers4

Linked