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

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

Click to expand...

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

Click to expand...

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

Click to expand...