Sorry for "spam", I hope that previous HTML form of mail (with bullet lists)
will be readable. There is, for sure and better readability for non-HTML clients, plain
text version of previous mail:
Hello,
I have some strange behaviout in LIKE query in SQLite. Letš see some very
simplified example:
Let's have a table
CREATE TABLE people (
firstname TEXT,
surname TEXT
);
and in it following data:
INSERT INTO people('Tomáš', 'Surname');
created by sqlite3_exec() function.
Then I use sqlite3_prepare_v2() function with for example this SQL query string:
"SELECT * FROM people WHERE firstname LIKE ?".
For binding I use: sqlite3_bind_text(stmt, 1, name.c_str(), -1, SQLITE_STATIC); where name is some
string for bellow, with "%" char at the end (for example name = "Tom%").
Then I use sqlite3_exec() function with callback, where sql string is
sqlite3_expanded_sql(stmt) from previous rows.
The problem is, that row with name "Tomáš" is selected also by following
strings (with CZ national chars) in variable name used in sqlite3_bind_text:
* "Tomě", but no longer with "Toměš"
* "Tomč", but no longer with "Tomčš"
* "Tomř" as well as "Tomřš"
* "Tomý" as well as "Tomýš"
* "Tomí", but no longer with "Tomíš"
* "Tomé", but no longer with "Toméš"
* "Tomů" as well as "Tomůš"
* "Tomú", but no longer with "Tomúš"
* "Tomď", but no longer with "Tomďš"
* "Tomň", but no longer with "Tomňš"
* "Tomáž"
* "Tomáť " (without space - it is written purposely cause hook above t char is
not so visible without the space)
+ all of combination where "Tom*š" is successfull and "Tomá*" is successfull:
* á = ř: Tomřž, Tomřť
* á = ý: Tomýž, Tomýť
* á = ů: Tomůž, Tomůť
* á = ï (see bellow): Tomïž, Tomïť
Following CZ national chars do not lead to select row with name "Tomáš", which
is correct behavior:
* "Tomš"
* "Tomž"
* "Tomť " (without space - it is written purposely cause hook above t char is
not so visible without the space)
For example the problem with other national chars from german language which also select
row with name "Tomáš":
* "Tomä", but no longer with "Tomäš"
* "Tomë", but no longer with "Tomëš"
* "Tomö", but no longer with "Tomöš"
* "Tomü" as well as "Tomüš"
Following national chars do not lead to select row name "Tomáš", which is
correct behavior:
* "Tomï"
Or it is only some misuse of sqlite3_*() functions?
Thank you, with best regards Tomáš Volf.
______________________________________________________________
Od: Vlczech - Tomáš Volf <vlcek...@centrum.cz>
Komu: <sqlite-users@mailinglists.sqlite.org>
Datum: 23.05.2017 21:28
Předmět: UTF8 LIKE stranges
Hello,
I have some strange behaviout in LIKE query in SQLite. Letš see some very
simplified example:
Let's have a table
CREATE TABLE people (
firstname TEXT,
surname TEXT
);
and in it following data:
INSERT INTO people('Tomáš', 'Surname');
created by sqlite3_exec() function.
Then I use sqlite3_prepare_v2() function with for example this SQL query string:
"SELECT * FROM people WHERE firstname LIKE ?".
For binding I use: sqlite3_bind_text(stmt, 1, name.c_str(), -1, SQLITE_STATIC); where name is some
string for bellow, with "%" char at the end (for example name = "Tom%").
Then I use sqlite3_exec() function with callback, where sql string is
sqlite3_expanded_sql(stmt) from previous rows.
The problem is, that row with name "Tomáš" is selected also by following
strings (with CZ national chars) in variable name used in sqlite3_bind_text:
"Tomě", but no longer with "Toměš""Tomč", but no longer with "Tomčš""Tomř" as well as "Tomřš""Tomý" as well as "Tomýš""Tomí", but no longer with "Tomíš""Tomé", but no longer with
"Toméš""Tomů" as well as "Tomůš""Tomú", but no longer with "Tomúš""Tomď", but no longer with "Tomďš""Tomň", but no longer with "Tomňš""Tomáž""Tomáť " (without space - it is written purposely cause hook above t
char is not so visible without the space)+ all of combination where "Tom*š" is successfull and "Tomá*" is successfull:
á = ř: Tomřž, Tomřťá = ý: Tomýž, Tomýťá = ů: Tomůž, Tomůťá = ï (see bellow): Tomïž,
TomïťFollowing CZ national chars do not lead to select row with name "Tomáš",
which is correct behavior:
"Tomš""Tomž""Tomť " (without space - it is written purposely cause hook above t char is
not so visible without the space)For example the problem with other national chars from german language which also
select row with name "Tomáš":
"Tomä", but no longer with "Tomäš""Tomë", but no longer with "Tomëš""Tomö", but no longer with
"Tomöš""Tomü" as well as "Tomüš"Followingnational chars do not lead to select row name "Tomáš", which is correct behavior:
"Tomï"
Or it is only some misuse of sqlite3_*() functions?
Thank you, with best regards Tomáš Volf.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
