Sorry you are right, the test I did was with % not with a *....just a
cut and paste error
Anyway it's tricky because I have to tell sqlite which index to use in
LIKE to fast search but I do not have to tell the index if start with %
or _ because otherwise the query is not executed.. I'll handle it...
Selea s.r.l.
Michele Pradella R&D
SELEA s.r.l.
Via Aldo Moro 69
Italy - 46019 Cicognara (MN)
Tel +39 0375 889091
Fax +39 0375 889080
*michele.pradella at selea.com* <mailto:michele.pradella at selea.com>
*http://www.selea.com*
Il 15/02/2016 10.51, Simon Slavin ha scritto:
> 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.
> _______________________________________________
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
