B4J Question Sqlite - LIKE case-insensitive for not English letters

[danchovasilev]

here is my sample code, but the result is not as expected

#Region Project Attributes
    #MainFormWidth: 600
    #MainFormHeight: 600
#End Region
#AdditionalJar:sqlite-jdbc-3.32.3.2
Sub Process_Globals
    Private fx As JFX
    Private MainForm As Form
    Private SQL As SQL
End Sub

Sub AppStart (Form1 As Form, Args() As String)
    MainForm = Form1
    MainForm.RootPane.LoadLayout("1") 'Load the layout file.
    MainForm.Show
    SQL.InitializeSQLite(File.DirApp, "dk6.db", True)
    SQL.ExecNonQuery("CREATE TABLE IF NOT EXISTS table1 (col1 TEXT COLLATE NOCASE)")
    SQL.ExecNonQuery("INSERT INTO table1 (col1) VALUES ('Мечо')")
    SQL.ExecNonQuery("INSERT INTO table1 (col1) VALUES ('Измервам')")
    SQL.ExecNonQuery("INSERT INTO table1 (col1) VALUES ('променям')")
    SQL.ExecNonQuery("INSERT INTO table1 (col1) VALUES ('мЕка')")
    SQL.ExecNonQuery("INSERT INTO table1 (col1) VALUES ('МЕТРО')")
    mmSearch
    SQL.ExecNonQuery("DELETE FROM table1")
End Sub

Sub mmSearch
    Dim mStr As String
    mStr="'%ме%'"
    Log("mStr->" & mStr)
    Dim tSql As String
    tSql="SELECT * FROM table1"
    tSql=tSql & " WHERE UPPER(col1) LIKE UPPER(" & mStr &")"
    Log(tSql)

    Dim Cursor As ResultSet
    Cursor = SQL.ExecQuery(tSql)
    If Cursor.IsInitialized Then
        Do While Cursor.NextRow
            Log(Cursor.GetString("col1"))
        Loop
    End If
    Cursor.Close
End Sub

'Return true to allow the default exceptions handler to handle the uncaught exception.
Sub Application_Error (Error As Exception, StackTrace As String) As Boolean
    Return True
End Sub

the result is: Измервам , променям
instead of everything: Мечо , Измервам , променям , мЕка , МЕТРО

 

[Erel]

Don't write queries like this. Code smell #4: https://www.b4x.com/android/forum/t...ommon-mistakes-and-other-tips.116651/#content

SQL Language Expressions

" SQLite only understands upper/lower case for ASCII characters by default. "

Possible workaround is to expand the like criteria:

Sub Process_Globals
    Private fx As JFX
    Private MainForm As Form
    Private SQL As SQL
End Sub

Sub AppStart (Form1 As Form, Args() As String)
    MainForm = Form1
    MainForm.Show
    SQL.InitializeSQLite(File.DirApp, "dk6.db", True)
    SQL.ExecNonQuery("CREATE TABLE IF NOT EXISTS table1 (col1 TEXT COLLATE NOCASE)")
    SQL.ExecNonQuery("INSERT INTO table1 (col1) VALUES ('Мечо')")
    SQL.ExecNonQuery("INSERT INTO table1 (col1) VALUES ('Измервам')")
    SQL.ExecNonQuery("INSERT INTO table1 (col1) VALUES ('променям')")
    SQL.ExecNonQuery("INSERT INTO table1 (col1) VALUES ('мЕка')")
    SQL.ExecNonQuery("INSERT INTO table1 (col1) VALUES ('МЕТРО')")
    mmSearch
    SQL.ExecNonQuery("DELETE FROM table1")
End Sub

Sub mmSearch
    Dim args As List
    args.Initialize
    Dim likes As String = CreateCaseInsensitiveArgs("col1", "Ме", args)
    Log(args)
    Dim Cursor As ResultSet = SQL.ExecQuery2("SELECT * FROM table1 WHERE " & likes, args)
    If Cursor.IsInitialized Then
        Do While Cursor.NextRow
            Log(Cursor.GetString("col1"))
        Loop
    End If
    Cursor.Close
End Sub

Sub CreateCaseInsensitiveArgs (col As String, q As String, args As List) As String
    Dim sb As StringBuilder
    sb.Initialize
    For i = 0 To Power(2, q.Length) - 1
        If i > 0 Then sb.Append(" OR ")
        sb.Append(col).Append(" LIKE ?")
    Next
    CreateCaseHelper("", q.ToLowerCase, args)
    Return sb.ToString    
End Sub

Sub CreateCaseHelper (prefix As String, q As String, args As List)
    If q.Length = 0 Then
        args.Add("%" & prefix & "%")
        Return
    End If
    Dim f As String = q.SubString2(0, 1)
    CreateCaseHelper(prefix & f, q.SubString(1), args)
    CreateCaseHelper(prefix & f.ToUpperCase, q.SubString(1), args)
End Sub

 

[danchovasilev]

Yes it works perfectly!
Thank you very much Erel.
I hope this post is useful to other users.

Don't write queries like this. Code smell #4: https://www.b4x.com/android/forum/t...ommon-mistakes-and-other-tips.116651/#content

SQL Language Expressions

" SQLite only understands upper/lower case for ASCII characters by default. "

Possible workaround is to expand the like criteria:

Sub Process_Globals
    Private fx As JFX
    Private MainForm As Form
    Private SQL As SQL
End Sub

Sub AppStart (Form1 As Form, Args() As String)
    MainForm = Form1
    MainForm.Show
    SQL.InitializeSQLite(File.DirApp, "dk6.db", True)
    SQL.ExecNonQuery("CREATE TABLE IF NOT EXISTS table1 (col1 TEXT COLLATE NOCASE)")
    SQL.ExecNonQuery("INSERT INTO table1 (col1) VALUES ('Мечо')")
    SQL.ExecNonQuery("INSERT INTO table1 (col1) VALUES ('Измервам')")
    SQL.ExecNonQuery("INSERT INTO table1 (col1) VALUES ('променям')")
    SQL.ExecNonQuery("INSERT INTO table1 (col1) VALUES ('мЕка')")
    SQL.ExecNonQuery("INSERT INTO table1 (col1) VALUES ('МЕТРО')")
    mmSearch
    SQL.ExecNonQuery("DELETE FROM table1")
End Sub

Sub mmSearch
    Dim args As List
    args.Initialize
    Dim likes As String = CreateCaseInsensitiveArgs("col1", "Ме", args)
    Log(args)
    Dim Cursor As ResultSet = SQL.ExecQuery2("SELECT * FROM table1 WHERE " & likes, args)
    If Cursor.IsInitialized Then
        Do While Cursor.NextRow
            Log(Cursor.GetString("col1"))
        Loop
    End If
    Cursor.Close
End Sub

Sub CreateCaseInsensitiveArgs (col As String, q As String, args As List) As String
    Dim sb As StringBuilder
    sb.Initialize
    For i = 0 To Power(2, q.Length) - 1
        If i > 0 Then sb.Append(" OR ")
        sb.Append(col).Append(" LIKE ?")
    Next
    CreateCaseHelper("", q.ToLowerCase, args)
    Return sb.ToString  
End Sub

Sub CreateCaseHelper (prefix As String, q As String, args As List)
    If q.Length = 0 Then
        args.Add("%" & prefix & "%")
        Return
    End If
    Dim f As String = q.SubString2(0, 1)
    CreateCaseHelper(prefix & f, q.SubString(1), args)
    CreateCaseHelper(prefix & f.ToUpperCase, q.SubString(1), args)
End Sub

Yes it works perfectly!
Thank you very much Erel.
I hope this post is useful to other users.

 

[tchart]

@danchovasilev just another option, have you tried the COLLATE NOCASE query option? I use this for case insensitive queries.

SELECT * FROM ... WHERE name = 'someone' COLLATE NOCASE

Datatypes In SQLite

 

[danchovasilev]

@danchovasilev just another option, have you tried the COLLATE NOCASE query option? I use this for case insensitive queries.

SELECT * FROM ... WHERE name = 'someone' COLLATE NOCASE

Datatypes In SQLite

thank you for the answer, but this method doesn't work for me

 

[Erel]

@danchovasilev just another option, have you tried the COLLATE NOCASE query option? I use this for case insensitive queries.

SELECT * FROM ... WHERE name = 'someone' COLLATE NOCASE

It cannot work with non-ASCII characters as SQLite doesn't have the needed unicode information.