SQLite Create function / non-ascii case insensitive

[aalekizoglou]

I am developing a SQLite DB on Android with text containing UTF8. Known problem in SQLite is the missing of case insensitive non-ASCII (UTF8) functions for sorting and LIKE comparison.

In the SQL library is there a way to impement a UDF with sqlite3_create_function?

What I usually implement in PHP is solution / implementation (thanks to blog.amartynov.ru) which works:

function lexa_ci_utf8_like($mask, $value) {
$mask = str_replace(
array("%", "_"),
array(".*?", "."),
preg_quote($mask, "/")

);
$mask = "/^$mask$/ui";
return preg_match($mask, $value);

}

$pdo->sqliteCreateFunction('like', "lexa_ci_utf8_like", 2);

 

[Erel]

You can only add functions to SQLite by rebuilding the engine.

Maybe you can create another column with the lower cased field.

 

[aalekizoglou]

Thanks Erel,

you see the problem is that you never know what the user will search. So maybe not letters will be all upper or lower case, but mixed as well. Like

'ΕταιΡΕία'

Some languages, as Greek, also have ancients, therefore it will be too difficult to keep all of them in different columns.

On the other hand MySQL has the entire UTF-8 engine build in, but I suppose I only have SQLite on the device. I am curious how other programs with build in DB achieve that kind of functionality with UTF-8 chars?

 

[rboeck]

Hi,

i dont understand your problem: i work with sqlite db's and had newer a problem like you; maybe its a problem with greek. I made my database with use of NOCASE statements in this manner:

[NAME1] TEXT ( 30 ) COLLATE NOCASE,

Maybe its different in greek..
Greetings
Reinhard

 

[aalekizoglou]

Hi,

i dont understand your problem: i work with sqlite db's and had newer a problem like you; maybe its a problem with greek. I made my database with use of NOCASE statements in this manner:

[NAME1] TEXT ( 30 ) COLLATE NOCASE,

Maybe its different in greek..
Greetings
Reinhard

Do you store UTF-8, non latin characters in a TEXT field? Try to do so, for example in the NAME1 field you said before store a Greek, German, Spanish, or whatever non-latin characters, e.g. "ΑΥΤΑ ΕΙΝΑΙ ελληνικα" - it says "THIS IS greek". Then try the following:

SELECT NAME1 FROM [youtable] WHERE NAME1 LIKE '%ΕΙΝΑΙ%" => this works UpperCase
SELECT NAME1 FROM [youtable] WHERE NAME1 LIKE '%ειναι%" => this doesn't work lowercase
SELECT NAME1 FROM [youtable] WHERE LOWER(NAME1) LIKE '%ειναι%" => this doesn't work lowercase does work for not-ascii

That means that NOCASE is case-insensitive for ASCII, and case sensitive for Greek or other chars.

In XDA I've found an addon to Android SQLite with COLLATE UNICODE, e.g.

SELECT * FROM [yourtable] ORDER BY NAME1 COLLATE UNICODE
Haven't tested that, yet, and don't know if it applies to WHERE CLAUSES

 

[rboeck]

That means that NOCASE is case-insensitive for ASCII, and case sensitive for Greek or other chars.

In german, we use three "umlaute" in lower- and uppercase and there we have the same problem like you; the normal ASCII codes lower- and uppercase works like expected. When i find an solution, i will post it later.
Greetings
Reinhard

 

[Erel]

you see the problem is that you never know what the user will search. So maybe not letters will be all upper or lower case, but mixed as well.

You can have a lower cased column. Then when the user enters the search query you will lower case it (in Basic4android) and search the lower cased column.

 

[aalekizoglou]

Erel,

this works. But now I need to know beforehand which TEXT columns should be searchable, to duplicate them with lowercase values.

Thanks,

 

[pkos]

Upper() and lower() sqlite functions don't work for unicode chars on most androids, due to missing ICU extensions.

However we can use the tsql replace function. Although this is ugly it actually works.

Sub Process_Globals
   Dim SQL1 As SQL
End Sub
Sub Activity_Create(FirstTime As Boolean)
   SQL1.Initialize(File.DirRootExternal,"local.db", True)
   'create 
   SQL1.ExecNonQuery("create table if not exists test ('line_column' int(3) not null,'text_column' varchar(20) not null, primary key ('line_column'))")
   'truncate
   SQL1.ExecNonQuery("delete from test")
   'compact db
   SQL1.ExecNonQuery("VACUUM")
   'insert
   SQL1.ExecNonQuery("insert into test values (1,'ΑΥΤΑ ΕΙΝΑΙ εΛΛηνικα')")
   SQL1.ExecNonQuery("insert into test values (2,'αυτά είναι Ελληνικά')")
   '
   'select
   Dim c As Cursor 
   Dim searchfor As String = "ελλ"
   c = SQL1.ExecQuery("select line_column,text_column from test where " & SqlUpper("text_column") & " like '%" & searchfor.ToUpperCase & "%'")
   For I = 0 To c.RowCount - 1 
      c.Position = I
      Msgbox (c.GetString2(1),c.GetInt2(0))
   Next
   c.Close   
   'end
   ExitApplication   
End Sub
Sub SqlUpper (Column As String) As String
   Return "replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(" & _ 
      "replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(" & _ 
      "replace(replace(replace(" & column & ",'α','Α'),'β','Β'),'γ','Γ'),'δ','Δ'),'ε','Ε'),'ζ','Ζ'),'η','Η'),'θ','Θ'),'ι','Ι')" & _
      ",'κ','Κ'),'λ','Λ'),'μ','Μ'),'ν','Ν'),'ξ','Ξ'),'ο','Ο'),'π','Π'),'ρ','Ρ'),'σ','Σ'),'τ','Τ'),'υ','Υ'),'φ','Φ'),'χ','Χ'),'ψ','Ψ')" & _
      ",'ω','Ω'),'ά','Ά'),'έ','Έ'),'ή','Ή'),'ί','Ί'),'ύ','Ύ'),'ώ','Ώ')"
    
End Sub