Like does not work with Russian symbols? (Swift, SQLite)

Question

I use like with lowercaseString and Russian symbols but LOWER doesn't convert them to lowercase in the query. I tried to create my own function but it didn't work for me. How to solve this problem?

Having studied the documentation of SQLite, I learned that you need to connect the ICU library. How can this be done in this plugin?

Library: stephencelis/SQLite.swift (https://github.com/stephencelis/SQLite.swift) Thanks for help.

// in name value: ПРИВЕТ from database
let search_name = "Привет"
user.filter(name.lowercaseString.like("%" + search_name.lowercased() + "%"))

Answer 1

SQLite LOWER is only for ASCII. If you want to get case insensitive for Russian (or any other symbols besides ASCII), use FTS3/FTS4 https://www.sqlite.org/fts3.html (or FTS5 https://www.sqlite.org/fts5.html).

SQLite.swift has the corresponding full text search modules https://github.com/stephencelis/SQLite.swift/blob/master/Documentation/Index.md#full-text-search

To use it in your project with existing database, you should make connection to virtual table via FTS module and filter the query using .match

    // CREATE VIRTUAL TABLE "table" USING fts4("row0", "row1"), if not exists
    try db.run(table.create(.FTS4(row0, row1), ifNotExists: true))
     
    // SELECT * FROM "table" WHERE "row0" MATCH 'textToMatch*'      
    try db.prepare(table.filter(row0.match("\(textToMatch)*")))
    
    // SELECT * FROM "table" WHERE "any row" MATCH 'textToMatch*' 
    try db.prepare(table.match("\(textToMatch)*")))