SQLite accent-insensitive search

Question

Is there any way to do an accent-insensitive LIKE query in SQLite? For example, this query:

SELECT * FROM users WHERE name LIKE "Andre%"

would return:

André the Giant
Andre Agassi
etc.

I'm using Qt with QSqlDatabase if it makes any difference.

Terbecki

Answer 1

Set up a collation using sqlite3_create_collation and then use it like this:

SELECT * FROM users WHERE name LIKE "Andre%" COLLATE NOACCENTS

Stanton

> How to setup sqlite3_create_collation in SQLite? Could you provide any link? – Cullen

This is really a bad answer. It's not explaining anything I have no idea why someone market it as answered. – Puncheon

Answer 2

You can create a custom collation function to remove accents from strings

Here I use sqlite3 library in Javascript for example

npm install sqlite3

const sqlite3 = require('sqlite3').verbose();
const db = new sqlite3.Database(':memory:'); // or 'example.db' for a file-based database

function removeAccents(str) {
    return str.normalize('NFD').replace(/[\u0300-\u036f]/g, '');
}

function noAccentsCollation(a, b) {
    const aNorm = removeAccents(a);
    const bNorm = removeAccents(b);
    if (aNorm < bNorm) return -1;
    if (aNorm > bNorm) return 1;
    return 0;
}

db.serialize(() => {
    // Register the collation
    db.run("SELECT load_extension('mod_spatialite')"); // Ensure extensions are enabled
    db.run("SELECT sqlite3_create_collation('NOACCENTS', noAccentsCollation)");

    // Create a sample table
    db.run(`
        CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY,
            name TEXT
        )
    `);

    // Insert sample data
    const stmt = db.prepare("INSERT INTO users (name) VALUES (?)");
    stmt.run('André the Giant');
    stmt.run('Andre Agassi');
    stmt.finalize();

    // Perform the accent-insensitive LIKE query
    db.all("SELECT * FROM users WHERE name LIKE 'Andre%' COLLATE NOACCENTS", (err, rows) => {
        if (err) {
            console.error(err);
            return;
        }
        rows.forEach(row => {
            console.log(row);
        });
    });
});

db.close();

Docile