Case insensitive sql like function

dolfin · 2016-01-26 12:58:18

Hi!

Is there any way to force like active case-insesitivly?
I have a REST server and trying to perform such request fom ajax client:
http://localhost:8080/root/guest/?select=*&where=Name like '%a%'
and I want to get both "...a..." and "...A..." values in the results.
How can I achieve this?

ab · 2016-01-26 13:16:38

Use the debugger in TSQLRestServer.URI to find out why the WHERE clause is not taken in account.
Perhaps there is an encoding problem at client side.

dolfin · 2016-01-26 14:09:20

I played a little bit more with where clause and found out that the problem happens only with cyrillic characters. For latin characters everything works fine. So comparison for letters 'я' and 'Я' is different.
How can I deal with this issue?

ab · 2016-01-26 14:12:22

AFAIK this is a SQLite3 known limitation.
See https://www.sqlite.org/faq.html#q18

The idea may be to use your own SQL function.
See http://synopse.info/files/html/Synopse% … ml#TITL_22
You may define Unicode-aware uppercase function for instance to SQlite3.pas.

dolfin · 2016-01-26 14:52:19

Thanks for the idea. But how I will force the framework to use my funciton instead of builtin one.
I'd not want to change source files.

mpv · 2016-01-27 18:00:48

This is a pain in every database (case-insensitive for Cyrillic letters).
For Oracle we execute statements:

ALTER SESSION SET NLS_COMP=LINGUISTIC; ALTER SESSION SET NLS_SORT=BINARY_CI;

and create functional indexes

For Postgre fallback to ILIKE operator.

For SQLite3 there is a good article here (Russian language) with Delphi samples. The main idea is to redefine LIKE function using sqlite3_create_function_v2() as AB note above

Last edited by mpv (2016-01-27 18:58:02)

pszawa · 2016-03-04 19:32:11

I had a similar problem with Polish letters. I solved this by modifying sqlite3.c - I extend sqlite3UpperToLower array (to handle all Polish characters just add about 120 items) and changed:

# define GlobUpperToLower(A)   if( !((A)&~0x7f) ){ A = sqlite3UpperToLower[A]; }

to:

# define GlobUpperToLower(A)   if( !((A)&~0x17f) ){ A = sqlite3UpperToLower[A]; }

previously I had such a solution like this:

static u32 tryPolishLetters(u32 A) {
    switch(A) {        
        case 0x17B: // Ż
            return 0x17C;
        case 0x179: // Ź
            return 0x17A;
        case 0x15A: // Ś
            return 0x15B;
        case 0xD3:  // Ó
            return 0xF3;
        case 0x143: // Ń
            return 0x144;
        case 0x141: // Ł
            return 0x142;
        case 0x118: // Ę
            return 0x119;
        case 0x106: // Ć
            return 0x107;
        case 0x104: // Ą
            return 0x105;
    }
    return A;
}

#if defined(SQLITE_EBCDIC)
# define sqlite3Utf8Read(A)    (*((*A)++))
# define GlobUpperToLower(A)   A = sqlite3UpperToLower[A]
#else
# define GlobUpperToLower(A)   if( !((A)&~0x7f) ){ A = sqlite3UpperToLower[A]; } else { A = tryPolishLetters(A); }
#endif

but i think the first is better.

It may not be the best solution, but it works

ab · 2016-03-05 08:51:03

AFAIR you could use your own SQL function for comparisons and even indexes, in latest version of SQLite3.
See https://www.sqlite.org/c3ref/create_collation.html and https://www.sqlite.org/syntax/indexed-column.html

pszawa · 2016-03-05 18:29:48

but it seems to me that this has no effect on the behavior of the LIKE operator, and it can only be changed by modifying the database engine

mORMot Open Source

#1 2016-01-26 12:58:18

Case insensitive sql like function

#2 2016-01-26 13:16:38

Re: Case insensitive sql like function

#3 2016-01-26 14:09:20

Re: Case insensitive sql like function

#4 2016-01-26 14:12:22

Re: Case insensitive sql like function

#5 2016-01-26 14:52:19

Re: Case insensitive sql like function

#6 2016-01-27 18:00:48

Re: Case insensitive sql like function

#7 2016-03-04 19:32:11

Re: Case insensitive sql like function

#8 2016-03-05 08:51:03

Re: Case insensitive sql like function

#9 2016-03-05 18:29:48

Re: Case insensitive sql like function

Board footer