SQLite Order with accent

Targets
1

I try to have an ordered result with a SQLite query from this simple query

SELECT NameFr FROM Plant
ORDER BY Plant.NameFR

I have some plant name starting with ‘é’ and those plants are not where there are supposed to be their are after the plants starting with ‘z’.

I found this solution on stack overflow for other development platform

SELECT NameFr FROM Plant
ORDER BY Plant.NameFR COLLATE BINARY

or

SELECT NameFr FROM Plant
ORDER BY Plant.NameFR COLLATE NOCASE UNICODE

In Xojo (I’m now working on iOS App) those queries return no row and no error.

Any idea ?

2

I would say “COLLATE NOCASE” is enough

3

Have a look at:

https://www.sqlite.org/datatype3.html

See section 7.

4

Capture d’écran 2023-03-09 à 20.31.57
Capture d’écran 2023-03-09 à 20.31.57724×452 21.1 KB

No is not :frowning:

5

The sqlite docs says that NOCASE will only fold the 26 ASCII characters.

I don’t know if BINARY will work for you, maybe depends how the É is constructed and maybe it will sort different if it is é (will not put É and é in order)

1 Like
6

seems you need to build your own collation to achieve this with sqlite
one entry point :

easier way would be to store a filtered text with no accent in another column and sort on it.

1 Like