On 15 Feb 2016, at 9:42am, Michele Pradella <michele.pradella at selea.com> wrote:
> CREATE TABLE car_plates (Id INTEGER PRIMARY KEY AUTOINCREMENT,FileName > VARCHAR(255),Plate VARCHAR(255)); > CREATE INDEX car_plates_plate on car_plates(Plate); > PRAGMA case_sensitive_like=ON; > > explain query plan SELECT * FROM car_plates INDEXED BY car_plates_plate WHERE > (Plate LIKE '*552*'); //OK > explain query plan SELECT * FROM car_plates INDEXED BY car_plates_plate WHERE > (Plate LIKE '__552*'); //Error: no query solution > explain query plan SELECT * FROM car_plates INDEXED BY car_plates_plate WHERE > (Plate LIKE '_*552*'); //Error: no query solution > explain query plan SELECT * FROM car_plates INDEXED BY car_plates_plate WHERE > (Plate LIKE '*_552*'); /OK > > I think is a strange behavior, but I'm going to read documentation. An underline character _ in the LIKE field means "any character". There is no way for SQLite to use an index to find a string that starts with any character. The index provides no help at all. The query will work perfectly if you don't insist on using that index. By the way, I'm guessing from the above that you think the asterisk * means "any string of characters". It doesn't. You should use the percent sign % for that. Simon.
