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

danchovasilev · Sep 19, 2020

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

my code:

#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 · Sep 20, 2020

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:

B4X:

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 · Sep 20, 2020

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

Erel said:

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:

B4X:

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 · Sep 21, 2020

@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 · Sep 22, 2020

tchart said:
@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 · Sep 22, 2020

tchart said:
@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.

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

danchovasilev

Member

Erel

B4X founder

danchovasilev

Member

tchart

Well-Known Member

danchovasilev

Member

Erel

B4X founder

Similar Threads