Here is another approach that might be faster: create table if not exists Songs(rowid integer primary key, title text); insert or replace into Songs(rowid,title) values (1, 'mi canción favorita me anima'), (2, 'my favorite cancion') ; select title from Songs where title regexp 'canci[óo]n';

In my experience, Amazon has one the most impressive language-independent search capabilities, and I have often wondered how they do it. Because of personal background, I routinely need to search for items on Amazon.es, Amazon.in and, of course, Amazon.com. I can enter search terms just about however I wish -- in Spanish ("curcuma"), in English (because my Spanish is not good and I only know the English term ("turmeric") or in Hinglish ("haldi", which is the Devanagari ह्लदी transliterated into the Latin script) -- and I find the items.

That is just a simple example, but really, Amazon is way more reliable than trying to first look up the term in a dictionary, especially when it comes to phrases. For example, my dictionary has entries for "bike" and "stem" but nothing for "bike stem" which is known as "potencia" in Spanish. Amazon finds it no matter how I enter it, accents or no accents.

The Hinglish search capabilities are really impressive and accurate. I can only imagine the massive, multi-language search database they must have not constructed but also the implementation that takes in any kind of input and spits out the mostly correct results.

This works…

…for now, perhaps, but what happens when you add a song title to your database including ñ or ç? And although you may only be expecting Spanish, what happens if a song with a "foreign" title is added, including other accents that you may not be expecting?

While LIKE is case-insensitive (by default), REPLACE is not, so your method is not currently catching anything with accented capital letters.

You also need to apply the same transformations on your user input, in case they do sometimes include accents.

Nested REPLACE quickly becomes a nightmare as you try to cover more and more characters.

but I was wondering if there is a better way.

SQLite (without the help of extensions) is not really the best choice for this sort of thing. Your favourite programming language probably has much better tools to deal with unicode and reducing accented characters to ascii "equivalents"¹, and I'd recommend using these tools (either independently or via an SQLite extension) rather than attempting the conversion in pure SQL.

A few different ways to handle it come to mind:

• Write (or find?) an extension to add a custom function to SQLite that will reduce accented characters to plain ascii.
• Write (or find?) an extension to replace the built-in LIKE function with one that ignores both case and accents when matching.
  • Can the ICU extension do this? I've never used it so I'm not sure, I just know it exists.
• Add extra columns to your table to hold the ascii version of each field and transform the data before adding it to the database. Compare/search using the ascii version, but display the original in the results.
  • You could manage the transformation in your favourite programming language before it hits the database, or via a custom function/extension on the way in.
  • These could perhaps be GENERATED … STORED columns using a custom function/extension to transform the text.

Note that handling this during SELECT means processing the transformation for every relevant column in every row of the database every time, which is going to be inefficient (but depending on the size of the database and user expectations, may not actually be a problem). On the other hand, transforming just once and storing the result will be more efficient but will nearly double the storage size for text columns. Which approach is better depends on whether speed or database size is more important to you.

1. ^{^} Noting that in some languages, (some) accented characters are considered to be entirely separate letters in their own right, not equivalent to the base ascii character

Can the ICU extension do this? I've never used it so I'm not sure, I just know it exists.

Good question. A quick read reveals nothing about diacritics or accents in the high level documentation. Similarly it's not clear if ICU regexps can handle accent-agnostic matching.

Your favourite programming language probably has much better tools to deal with unicode and reducing accented characters to ascii "equivalents", and I'd recommend using these tools (either independently or via an SQLite extension) rather than attempting the conversion in pure SQL.

Second this, especially as the use of LIKE '%foo%' in the current approach effectively requires a table scan for every search anyway (ain't no simple index that helps with a wildcard prefix).

It might also be worth reading up on unicode normalization forms to provide some consistency when inserting records into the database.

I was wondering if there is a better way.

There is, but it is at least partially outside the scope of SQLite.

You need to create a second column with the accent-less version of the titles (and equivalent for other text things that they search on). The de-accenting can be done by normalizing the text to NFD (Unicode normalization form, decomposed) and stripping the pure accent characters (especially of class Mn, "Non-spacing marks") from that. That's the part which SQLite doesn't provide (and isn't going to; the libraries for doing it are a bit too large given that many use cases for SQLite don't need the functionality). Once you've got that function, you can then prepare the text on which you search; there's a bunch of options for that. In theory, you could expose the normalize-strip function as a User Defined Function to SQLite, but you probably don't need to do that; I suspect it's not a good idea in performance terms to be running LIKE against things that are computed there and then (no way to implement it without a full table scan).

You'll probably want to also do the normalize-strip trick for the search terms entered by users. You can't count on them not using accents even if it is common for them to do so.

Here's a download link. Included .dll is 32-bit, ready for use.

https://u.pcloud.link/publink/show?code=XZtjitVZuJD0RhF1eDpAtSf38l3tmSbnFeS7

You are, of course, posing the most difficult problem -- an unbounded instring brute force search. Presumably it is your "replace(replace(replace(..." crud you want to fix.

Unfortunately, the default ICU implementation does not appear to fold accents, only case. You can, however, code a translitterator to replace your "replace(replace(replace(..." monstrosity.

If you were to forgo the brute force instring search and use a "proper index" to search "from the beginning of the field", then you would simply need to use a proper collation, eg: en-u-ks-level1 or es-u-ks-level1 (und-u-ks-level1 would probably even be correct) when you define the column. Thereafter the column is case and accent preserving, but case and accent ignoring.

You could also use an ICU transliterator to do the case and mark folding. The following transliteration would do the trick:
"Latin;NFKD;[:Nonspacing Mark:]Remove;Lower;NFKC;ascii"

That is, convert all the characters to latin, decompose, remove non-spacing marks (accents), convert to lowercase, recompose, convert to ascii.

An example ICU function might look as follows:

static void icuTransliterate(sqlite3_context *ctx, int argc, sqlite3_value **argv)
{
    UParseError pe;
    UTransliterator *t = 0;
    UErrorCode status = U_ZERO_ERROR;
    UChar *xform_id;
    UChar *buffer;
    int n, limit, bs;
    int slot = -1229149472; /* (unicode('I')*16777216 + unicode('C')*65536 + unicode('U')*256 + unicode(' ')) */
    const char *data;

    if (argc == 2) slot = 1;
    t = sqlite3_get_auxdata(ctx, slot);
    if (!t) {
        if (argc == 1) {
            xform_id = L"Latin;NFKD;[:Nonspacing Mark:]Remove;Lower;NFKC;ascii";
        } else {
            xform_id = (UChar*)sqlite3_value_text16(argv[1]);
        }
        t = utrans_openU(xform_id, -1, UTRANS_FORWARD, NULL, -1, &pe, &status);
        if (U_FAILURE(status)) {
            icuFunctionError(ctx, "utrans_openU", status);
            return;
        }
        sqlite3_set_auxdata(ctx, slot, t, icuTransliterateDelete);
        t = sqlite3_get_auxdata(ctx, slot);
        if (!t) {
           sqlite3_result_error_nomem(ctx);
           return;
       }
    }
    data = sqlite3_value_text16(argv[0]);
    n = sqlite3_value_bytes16(argv[0]);
    limit = n/2;
    bs = sizeof(UChar) * n;
    buffer = sqlite3_malloc(bs);
    memcpy(buffer, data, n);
    n /= 2;
    utrans_transUChars(t, buffer, &n, n*2, 0, &limit, &status);
    if (U_FAILURE(status)) {
        sqlite3_free(buffer);
        icuFunctionError(ctx, "utrans_transUChars", status);
        return;
    }
    sqlite3_result_text16(ctx, buffer, limit*2, SQLITE_TRANSIENT);
    sqlite3_free(buffer);
}

    {"fold",   1, SQLITE_ANY|SQLITEICU_EXTRAFLAGS,         0, icuTransliterate},
    {"icu_trans",2,SQLITE_ANY|SQLITEICU_EXTRAFLAGS,        0, icuTransliterate},

You could then use:

SELECT * 
  FROM Songs 
 WHERE st = 1
   AND (
        fold(title) LIKE '%cancion%' OR
        fold(body) LIKE '%cancion%' OR
        fold(songType) LIKE '%cancion%' OR
        fold(AgendaType) LIKE '%cancion%' OR
        fold(key) LIKE '%cancion%'
       )
;

Thank you all for all those wonderful comments and suggestions. I am going to read and see what the best solution for me is.

BTW, I started using GLOB and REGEXP, but, because the input is 99% lowercase, this happens:

sqlite> select a from t where glob('*dios*',a);
VM-steps: 15
Run Time: real 0.000 user 0.000000 sys 0.000000
sqlite> select a from t where glob('*Dios*',a);
┌────────┐
│   a    │
├────────┤
│ 'Dios' │
└────────┘
VM-steps: 17
Run Time: real 0.000 user 0.000000 sys 0.000000

OR

sqlite> select a from t where regexp(a,'*dios*');
VM-steps: 15
Run Time: real 0.000 user 0.000000 sys 0.000000
sqlite> select a from t where regexp(a,'*Dios*');
┌────────┐
│   a    │
├────────┤
│ 'Dios' │
└────────┘
VM-steps: 17
Run Time: real 0.015 user 0.000000 sys 0.000000

So, I would have to set a to lowercase and that would be a lot of text processing. Unless there is a GLOB or REGEXP option to search on just lowercase. I couldn't find one on the docs. So, LIKE is the best option for me, right now.

sqlite> select a from t where a like '%dios%';
┌────────┐
│   a    │
├────────┤
│ 'Dios' │
└────────┘
VM-steps: 17
Run Time: real 0.000 user 0.000000 sys 0.000000

Thanks.

Some RDBMS (Oracle, SQL Server, Postgresql, maybe others) offer the built-in function "translate", which returns "the string from the first argument after the characters specified in the second argument are translated into the characters specified in the third argument" (copied from Google).

Fairly easy to code as a UDF if not available.

Example CTE for you to experiment with.

This is a bit cack-handed I'm afraid - pretty sure it doesn't need the 'i' (index) column on the map to work, but this was the only way I could figure it out - also the logic is a bit odd - the non-recursive bit of the CTE (_repl) runs over the string with the first item to replace, then after that it counts down from MAX to 2.

Runs fast enough on this small dataset, I don't suppose this will scale that well it things got more complex.

WITH
	map(i,s,r) AS (
		VALUES 
			(1,'á','a'),
			(2,'é','e'),
			(3,'í','i'),
			(4,'ó','o'),
			(5,'ú','u')),
	search(term) AS ( 
		VALUES	
			('canción'),
			('áéíóú')),
	_repl(i,_temp) AS
		( SELECT (SELECT MAX(i) FROM map), REPLACE(term,s,r) FROM search JOIN map WHERE i=1
			UNION ALL
		  SELECT i-1, REPLACE(_temp,s,r) FROM _repl JOIN map USING(i) 
		  LIMIT 100 -- safety net
	)
SELECT * FROM _repl WHERE i=1

To use in application - you could replace the 'search' CTE with a parameter like:

[...]
search(term) AS ( VALUES(:search) ),
[...]

And the 'map' table could be a physical table of course, I just added to CTE to provide complete example.

Example output:

SQLite version 3.37.2 2022-01-06 13:25:41
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .mode markdown
sqlite> .read eg.sql

• EDIT: changed COUNT(*) to MAX(i), fewer assumptions.