Sqlite Like % and _

SQLite Like % and _

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.

How to perform SQLite LIKE queries with wildcards not read as wildcards

In SQL, you can escape special characters in the LIKE pattern if you declare some escape character with ESCAPE:

SELECT * FROM MyTable WHERE Name LIKE 'te\_t' ESCAPE '\'

(see the documentation)

SQLite query, wildcards and column values containing % or _ characters

Assuming the table name is candidate, you can use sqlite keyword ESCAPE to escape % and _, for more references see the sqlite docs.

SELECT * FROM candidate WHERE col2 LIKE '%\%' ESCAPE '\'

Escape wildcards (%, _) in SQLite LIKE without sacrificing index use?

An index can only be used with a LIKE clause when the % is at the end so that SQLite can rewrite it to two simple comparisons (as shown in the EXPLAIN output).

Therefore, to get the same effect, write the comparisons yourself.
This requires that you construct some string that is guaranteed to compare 'larger' than any of the matched values (beware of non-ASCII characters).
Instead of:

... WHERE Name LIKE 'Text%'

use:

... WHERE Name BETWEEN 'Text' AND 'Textzzzzzzzzzzzzz'

or, as parameter:

... WHERE Name BETWEEN @Pattern AND @Pattern || 'zzzzzzzzzz'

(This construct never needs escapes. :)

SQL with LIKE doesn't return anything (SQLite)

The underscore character is a wildcard in the LIKE clause for a match of any single character. If you want to look for the literal underscore, you need to escape it so it is not treated as a wildcard.

...WHERE ModelName NOT LIKE '\_%' ESCAPE '\'

Does SQLite support character range with [ ]?

SQLite does not support this SQL Server - like functionallity that you want.

You can do it with SUBSTR():

WHERE SUBSTR(LastName, 1, 1) BETWEEN 'B' AND 'L'

or:

WHERE LastName >= 'B' AND LastName < 'M'

How to do NOT in LIKE query in SQLite?

You cannot construct a LIKE pattern that behaves the same as a NOT LIKE pattern; the only special characters are % and _.

That someone else's code is just not capable of doing what you want.

---

Related Topics