SQLiteDatabase accents on vowels

Databases
1

I’ve a search that works if no accents are on vowels, that’s good for english.

Dim stmt As SQLitePreparedStatement
s = “%” + s + “%”
stmt = SQLitePreparedStatement(MyDataBase.Prepare(“SELECT * FROM Frases WHERE frase like ?”))

etc.

On other languages if user writes “do” all records that contains “do” are found but not records with “d”, or "“d” or any accent on vowel
I read somewhere that I should alter database like:

MyDataBase.SQLExecute (“ALTER DATABASE [MyDataBase] COLLATE Modern_Spanish_CI_AI”)

But this did not worked.

Any suggestion?.

2

you must add the collation “modern_spanish” to your database, and I also would like to know how to do it (for french)
the sqlite in xojo does not support local accents, they have to change the sqlite compilation
and it would add 20MB to the applications and they don’t want to do it for now.
ok for english people, but problems for the rest of the world…
<https://xojo.com/issue/26859>

you could try “select * from frases where frase like ? collate nocase”, it may work.
but sorting is not possible with xojo sqlite.

3

Thak you Jean, “collate nocase” do not worked for the selection.
I see this is a general problem with SQLite

I tried also to intercept the vowels in the string the user is typing and convert the vowels (I also read somewhere that using [o]) like for example:

if found an “o” convert as [o]
if found an “e” convert to [e]
etc, so if is written “done” the search is on “d[o]n[e]”
etc. but no luck.

4

the only way to do it today, is to store an ascii version of the text you’re searching for
you store a field in the database with the complete word ex: “tourdi”
and another field without the accents : “etourdi”
and you make your search with the ascii field
it takes twice the space needed, but it works.

5

That’s good I’ll try to implement this, thank you.

6

I’ve solved this in SQL Server using

COLLATE Cyrillic_General_CI_AI

SELECT * FROM Frases WHERE CONVERT(VARCHAR(max), frase) COLLATE Cyrillic_General_CI_AI like …

Hope this could help in SQLite

7

I used that Jean-Yves Pochez suggested, and added a field with the ascii version. That works.
I’ll try your suggestion but I think SQLite is not the same…

8

collation is very specific to each database engine
each engine deals with it in a different way
and with sqlite, we simply cannot use it the international collations are not included in xojo plugin
see the feedback above.

9

actually thomas posted a way to do this on his blog some time ago