Case insensitive LIKE (and ORDER) in SQLite, above ASCII [SOLVED by
LacaK]
lazarus / 16-Databases

----------------------------------------------------------------------
totya · 2016-10-02 10:40

Hi!

I created this topic for this important problem, previous similar
topic this.

In normal case, this code:

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i SELECT *
FROM MYTABLE WHERE MYDATAFIELD LIKE "%searchedtext%" ORDER BY
MYDATAFIELD;
works only with ascII code.

For example see these caharceters (from hungarian language):
lower case version:
"á"
same as uppecase version:
"Á"

Like "%á%" doesn't work records which contains "Á".

This is known problem with SQLITE, see this faq

Quote(18) Case-insensitive matching of Unicode characters does not
work.

The default configuration of SQLite only supports case-insensitive
comparisons of ASCII characters. The reason for this is that doing
full Unicode case-insensitive comparisons and case conversions
requires tables and logic that would nearly double the size of the
SQLite library. The SQLite developers reason that any application that
needs full Unicode case support probably already has the necessary
tables and functions and so SQLite should not take up space to
duplicate this ability.
Instead of providing full Unicode case support by default, SQLite
provides the ability to link against external Unicode comparison and
conversion routines. The application can overload the built-in NOCASE
collating sequence (using sqlite3_create_collation()) and the built-in
like(), upper(), and lower() functions (using
sqlite3_create_function()). The SQLite source code includes an "ICU"
extension that does these overloads. Or, developers can write their
own overloads based on their own Unicode-aware comparison routines
already contained within their project.
So, I'd like to know, how can I use LIKE operator with non ASCII text
in SQLITE, with Lazarus.

From this faq I see two different ways:

1. collation , see this message

2. with ICU

Anyone can you show me Lazarus code examples for these ways?

The best, if the solution works with all UTF8 chars.

Thanks!

Remark:
- I need SQLite really, because I need local file, without any other
(server) installation, like as Firebird.

----------------------------------------------------------------------
LacaK · 2016-10-03 06:59

I have added
http://wiki.freepascal.org/SQLite#Creating_user_defined_collations
But this does not solve LIKE operator  , which requires more work ...

In short you must define user function, which will overload default
LIKE() function:

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
UTF8xLike(ctx: psqlite3_context; N: cint; V: ppsqlite3_value);
cdecl;var Y1,X1: AnsiString;    Y2,X2: UTF8String;begin  // like(X,Y)
= Y LIKE X  X1 := sqlite3_value_text(V[0]);  Y1 :=
sqlite3_value_text(V[1]);  X2 :=
UTF8Encode(AnsiLowerCase(UTF8Decode(X1)));  Y2 :=
UTF8Encode(AnsiLowerCase(UTF8Decode(Y1)));  sqlite3_result_int(ctx,
ord(sqlite3_strlike(PAnsiChar(X2), PAnsiChar(Y2), 0)=0));end; 
then register this user function:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
sqlite3_create_function(SQLite3.Handle, 'like', 2, SQLITE_UTF8 or
SQLITE_DETERMINISTIC, nil, @UTF8xLike, nil, nil); 
But it will not work as is, because it requires also additions to
SQLite3 header file

Another approach can be overload default LOWER() function:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
UTF8xLower(ctx: psqlite3_context; N: cint; V: ppsqlite3_value);
cdecl;var S: AnsiString;begin  SetString(S, sqlite3_value_text(V[0]),
sqlite3_value_bytes(V[0]));  S :=
UTF8Encode(AnsiLowerCase(UTF8Decode(S)));  sqlite3_result_text(ctx,
PAnsiChar(S), Length(S),
sqlite3_destructor_type(SQLITE_TRANSIENT));end; 
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i  //
override default LOWER function 
sqlite3_create_function(SQLite3.Handle, 'lower', 1, SQLITE_UTF8 or
SQLITE_DETERMINISTIC, nil, @UTF8xLower, nil, nil); 
and then use:
  lower(column1) like '%á%'

----------------------------------------------------------------------
totya · 2016-10-03 21:46

Hi!

Big thanks to you!

This second method is working correctly with SQL LIKE, with all
hungarian chars, above ASCII, like as:
Quoteöüóűőúéáí ÖÜÓŐÚÉÁÍ
Test data about:
Quoteööö
ÖÖÖ
üüü
ÜÜÜ
óóó
ÓÓÓ
űűű
ŰŰŰ
őőő
ŐŐŐ
úúú
ÚÚÚ
ééé
ÉÉÉ
ááá
ÁÁÁ
ííí
ÍÍÍ

When I type to searchbar these chars in my app, I get two records
certainly, upper and lowercase verions of these chars. I'm happy

I'm use TSQLite3Dataset, and I needed some small thing to compile your
excellent code, for example:

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i uses  
SqLite3, ctypes;
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i const  
SQLITE_DETERMINISTIC =$800;  
See this link.

In this code:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
sqlite3_create_function(SQLite3.Handle, 'lower', 1, SQLITE_UTF8 or
SQLITE_DETERMINISTIC, nil, @UTF8xLower, nil, nil);
Because I use TSQLite3Dataset, I need to swap:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
SQLite3.HandleCode: Pascal  [Select][+][-]window.onload =
function(){var x1 = document.getElementById("main_content_section");
if (x1) { var x = document.getElementsByClassName("geshi");for (var i
= 0; i Sqlite3Dataset1.SqliteHandle
SQL code:

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i case
SQLSearchMethodRadioGroup.ItemIndex of             0: SQL:= 'SELECT *
FROM '+TableName+                     ' WHERE
'+'lower('+DataFieldName+')'+                     ' LIKE '+SearchStr+ 
                   ' ORDER BY '+'lower('+DataFieldName+')'+' ASC'+   
                ';';           end;                               
So, everything is fine, except one thing, but this isn't my question
before. As you can see, I use ORDER BY ASC, but the result is
incorrectly for above asc II chars, with or without lower()
conversion. Do you have any idea to the proper sorting? Like as this
topic.

But this isn't my original question, big thanks again for your answer!

----------------------------------------------------------------------
LacaK · 2016-10-04 05:20

Quote from: totya on October 03, 2016, 11:46:15 pmAs you can see, I
use ORDER BY ASC, but the result is incorrectly for above asc II
chars, with or without lower() conversion. Do you have any idea to the
proper sorting?

Look at
http://wiki.freepascal.org/SQLite#Creating_user_defined_collations

Then use:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
sqlite3_create_collation(SQLite3.Handle, 'UTF8_CI', SQLITE_UTF8, nil,
@UTF8xCompare_CI);
and in SQL:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i SQL:=
'SELECT * FROM '+TableName+' WHERE '+'lower('+DataFieldName+')'+' LIKE
'+SearchStr+' ORDER BY '+DataFieldName+' COLLATE UTF8_CI ASC';

----------------------------------------------------------------------
totya · 2016-10-05 00:49

Hi!

Many thanks to you!

It seems to me it's working!

Remarks:

- I use it with TSQLite3Dataset.

- Sqlite3dyn unit in uses line cause crash (sigsev error), but with
sqlite3 in uses line seems to me it's okay.

- In my country the accents chars order is important, for example the
"a" smaller than "á" and so on, but the sorting method handle these
chars with same chars. But it isn't a big problem, because the Excel
works similar too. But I can create correct order, if I will modify
the UTF8xCompare_CI function.

Thanks again!

I create unit for usage:

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i unit
Unit_SQLite3Dataset_LanguageSupport; {SQLite3LanguageSupport made by
totya from LacaK code, see forum:http://forum.lazarus.freepascal.org/i
ndex.php/topic,34259.0.html Usage: (after table
created)SQLite3LanguageSupport
(Sqlite3Dataset1.SqliteHandle); changelog:v1.0 (2016.10.05 02:30)
initial version totya remarks:- Tested with TSQLite3Dataset (rev34598)
on Win7 x64, Lazarus 1.6- The recommended sqlite3dyn unit doesn't
need, but if present, I got sigsev error.} {$mode
objfpc}{$H+} interface uses  Classes, SysUtils,   sqlite3, //
 sqlite3dyn,   ctypes; procedure SQLite3Dataset_LanguageSupport (const
_Handle: pointer); implementation const  SQLITE_UTF8= $01; 
SQLITE_DETERMINISTIC =$800; // http://forum.lazarus.freepascal.org/ind
ex.php/topic,34259.msg224029.html#msg224029procedure UTF8xLower (ctx:
psqlite3_context; {%H-}N: cint; V: ppsqlite3_value); cdecl;var S:
AnsiString;begin  SetString(S, sqlite3_value_text(V[0]),
sqlite3_value_bytes(V[0]));  S :=
UTF8Encode(AnsiLowerCase(UTF8Decode(S)));  sqlite3_result_text(ctx,
PAnsiChar(S), Length(S),
sqlite3_destructor_type(SQLITE_TRANSIENT));end; // utf8 case-sensitive
compare callback function// http://wiki.freepascal.org/SQLite#Creating
_user_defined_collationsfunction UTF8xCompare_CS ({%H-}user: pointer;
len1: longint; data1: pointer; len2: longint; data2: pointer):
longint; cdecl;var S1, S2: AnsiString;begin  SetString(S1, data1,
len1);  SetString(S2, data2, len2);  Result :=
UnicodeCompareStr(UTF8Decode(S1), UTF8Decode(S2));end; // utf8 case-
insensitive compare callback function// http://wiki.freepascal.org/SQL
ite#Creating_user_defined_collationsfunction UTF8xCompare_CIS
({%H-}user: pointer; len1: longint; data1: pointer; len2: longint;
data2: pointer): longint; cdecl;var S1, S2: AnsiString;begin 
SetString(S1, data1, len1);  SetString(S2, data2, len2);  Result :=
UnicodeCompareText(UTF8Decode(S1), UTF8Decode(S2));end; // http://foru
m.lazarus.freepascal.org/index.php/topic,34259.msg224029.html#msg22402
9// http://forum.lazarus.freepascal.org/index.php/topic,34259.msg22415
2.html#msg224152procedure SQLite3Dataset_LanguageSupport (const
_Handle: pointer);begin  if Assigned(_Handle)  then    begin     
sqlite3_create_function        (_Handle,        'lower',        1,   
   SQLITE_UTF8 or SQLITE_DETERMINISTIC,        nil,       
@UTF8xLower,        nil, nil);       sqlite3_create_collation(_Handle,
'UTF8_CS', SQLITE_UTF8, nil, @UTF8xCompare_CS);     
sqlite3_create_collation(_Handle, 'UTF8_CIS', SQLITE_UTF8, nil,
@UTF8xCompare_CIS);    end  else raise Exception.Create('Unassigned
handle in
SQLite3Dataset_LanguageSupport');end; initialization finalization end.

----------------------------------------------------------------------
LacaK · 2016-10-05 06:44

Quote from: totya on October 05, 2016, 02:49:06 am- In my country the
accents chars order is important, for example the "a" smaller than "á"
and so on, but the sorting method handle these chars with same chars.
But it isn't a big problem, because the Excel works similar too. But I
can create correct order, if I will modify the UTF8xCompare_CI
function.

For me it works as expected ("a" is before "á").
How do you have modified UTF8xCompare_CI ?

----------------------------------------------------------------------
totya · 2016-10-05 17:05

Quote from: LacaK on October 05, 2016, 08:44:59 amQuote from: totya on
October 05, 2016, 02:49:06 am- In my country the accents chars order
is important, for example the "a" smaller than "á" and so on, but the
sorting method handle these chars with same chars. But it isn't a big
problem, because the Excel works similar too. But I can create correct
order, if I will modify the UTF8xCompare_CI function.

For me it works as expected ("a" is before "á").
How do you have modified UTF8xCompare_CI ?

Hi!

Thank you for the answer!

My full unit you can see in my previous post... I didn't change
anything on it.

My words test for example:

Quoteaffe
ale
abd
ács
ábbas

The result after sorting (bad):

Quoteábbas
abd
ács
affe
ale

See attached demp project.

But as I said, the result same as in MSExcel.

----------------------------------------------------------------------
LacaK · 2016-10-07 11:36

Probably in Hungarian collation is "a"="á" (in 1st level ordering)?

https://en.wikipedia.org/wiki/Alphabetical_order:
" In collating, accented vowels are equivalent with their non-accented
counterparts and double and triple characters follow their single
originals. Hungarian alphabetic order is: A=Á, B, C, Cs, D, Dz, Dzs,
E=É, F, G, Gy, H, I=Í, J, K, L, Ly, M, N, Ny, O=Ó, Ö=Ő, P, Q, R, S,
Sz, T, Ty, U=Ú, Ü=Ű, V, W, X, Y, Z, Zs."

https://en.wikipedia.org/wiki/Hungarian_alphabet#Alphabetical_ordering
_.28collation.29

----------------------------------------------------------------------
totya · 2016-10-07 13:22

Hi!

Thanks for the answer!

After I read your post, I searched on the web many times. Well, the
rule, what you wrote, that's correct. (LOL!)

Rules in my language (the best trustworthy source)
Rule
Rule II

Otherwise as I see on forums, many peoples think the correct sort
order in my language similar of this: ("a".."á" etc). I asked my
younger sister, and two my friends, and both told me: words with "a"
the first, and the second is with "á"...

But once again, you are right, because this is the rule, but this rule
is very ugly and pointless, and hard to use.

Okay, but if I want own sort with "non-rule logic order" what is the
shortest way?
Okay, I understand, this isn't SQL related question, this is
UnicodeCompare(x) function question.

----------------------------------------------------------------------
LacaK · 2016-10-08 15:47

Quote from: totya on October 07, 2016, 03:22:22 pmBut once again, you
are right, because this is the rule, but this rule is very ugly and
pointless, and hard to use.
But it is like your language ordering is officially defined. No sense
to fight against it.
If you want introduce your own ordering you can do it by comparing
char by char like:

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i function
UTF8xCompare_CI2 ({%H-}user: pointer; len1: longint; data1: pointer;
len2: longint; data2: pointer): longint; cdecl;var S1, S2:
UTF8String;    US1, US2: UnicodeString;    i,l: integer;begin 
SetString(S1, data1, len1);  SetString(S2, data2, len2);  US1 :=
UTF8Decode(S1);  US2 := UTF8Decode(S2);  if Length(US1) < Length(US2)
then    l := Length(US1)  else    l := Length(US2);  i := 1;  Result
:= 0;  while (Result=0) and (i <= l) do begin    Result :=
UnicodeCompareText(US1[i], US2[i]);    Inc(i);  end;  if Result = 0
then    Result := Length(US1) - Length(US2);end;

----------------------------------------------------------------------
totya · 2016-10-09 18:57

Hi LacaK!

With your sample, I sucessfully created the own order method, and it's
working correctly, but that wasn't too easy... My lucky, hungarian
chars doesn't need utf8 or unicode codepage, so I convert string to my
cp with UTF8ToCP1250, then I use (double) case with charcode to
compare. Surprise, the sort isn't much slower, what I thinked before.

Your help in this topik  is invaluable, thank you very much for your
very big help!

----------------------------------------------------------------------
totya · 2017-01-15 16:44

Quote from: LacaK on October 03, 2016, 08:59:04 amCode: Pascal
 [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
UTF8xLower(ctx: psqlite3_context; N: cint; V: ppsqlite3_value);
cdecl;var S: AnsiString;begin  SetString(S, sqlite3_value_text(V[0]),
sqlite3_value_bytes(V[0]));  S :=
UTF8Encode(AnsiLowerCase(UTF8Decode(S)));  sqlite3_result_text(ctx,
PAnsiChar(S), Length(S),
sqlite3_destructor_type(SQLITE_TRANSIENT));end; 
Hi!

Thanks again! I tested (revised/retested) this above code, and this
line:

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i S :=
UTF8Encode(AnsiLowerCase(UTF8Decode(S)));
Slightly overcomplicated for me. This code equivalent with these
simpler codes:

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i S:=
UTF8LowerCase(S);
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i S:=
AnsiLowerCase(S);
I tested it with the all of UTF8 chars under 100.000, where the lower
and upper case is different, these are 828 UTF8 chars.

This code produce bad result certainly:

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i S:=
LowerCase(S);