How to set Sqlite to be case insensitive when unicode string comparing?

[Igor Kuznetsov]

Official SQLite docs state:

SQLite only understands upper/lower case for ASCII characters by default. The LIKE operator is case sensitive by default for unicode characters that are beyond the ASCII range.

I want to select records from local database by unicode string matching. But if I use 'LIKE' in the where clause, I found that sqlite is not work UPPER/LOWER with unicode. Can anyone tell me how to use string comparing case-insensitive? Thank you very much!

 

[Douglas]

You can use the upper or lower function to convert both the strings to compare. See this example:

Select Id, Name From Customers Where Upper(Name) Like Upper('%Igor%')

or provide the term in uppercase, letting the function to operate only in the column table:

Select Id, Name From Customers Where Upper(Name) Like '%IGOR%'

[Igor Kuznetsov]

Youre example not correct work with UNICODE.

My example string with russian strings (utf8):

SELECT places.title FROM places WHERE UPPER(places.title) LIKE '%ДЕД%';

this no work.

пятница, 21 июня 2013 г., 20:40:50 UTC+4 пользователь Douglas написал:

[Douglas]

I had the same problem using Portuguese Brazilian strings. Although the characters were special characteres (not ASCII-only), the input received from the Rhodes view was encoded with another encoding (Windows1252), so I needed to force it to be encoded like UTF-8:

    term = @params['term'].
      force_encoding('utf-8').
      gsub("'", "''").
      upcase
    ...
    Select * From Cities Where Upper(Name) Like Upper('%#{term}%')

It's important to verify whether your database encoding is also set to UTF-8 when doing this.

I hope this will help you.

[Igor Kuznetsov]

суббота, 22 июня 2013 г., 15:55:31 UTC+4 пользователь Douglas написал:

I had the same problem using Portuguese Brazilian strings. Although the characters were special characteres (not ASCII-only), the input received from the Rhodes view was encoded with another encoding (Windows1252), so I needed to force it to be encoded like UTF-8:

    term = @params['term'].
      force_encoding('utf-8').
      gsub("'", "''").
      upcase
    ...
    Select * From Cities Where Upper(Name) Like Upper('%#{term}%')

Hi Douglas!

My example contained uppercase utf8 string (russian): '%ДЕД%'

In the SQLite not work UPPER function with utf8 values in database.

I tried to enable ICU in Rhodes, result: many errors build.

Found sqlite3.c hack/patch (for russian symbols):

- # define GlogUpperToLower(A)   if( !((A)&~0x7f) ){ A = sqlite3UpperToLower[A]; }
+ # define GlogUpperToLower(A)   if( !((A)&~0x7f) ){ A = sqlite3UpperToLower[A]; } if ( A >= 0x0410 && A <= 0x042f) { A += 0x20; }

No pretty, but work!!!

But stiil wants to enable ICU for all platforms.