I have tables which involve field content other than in English. When I sort/index records, I typically find that any accented character is ordered after the entire ASCII alphabet, e.g.
Pan…
Phil…
Port…
Psy…
Péri…
when what I want is
Pan…
Péri…
Phil…
Port…
Psy…
Is there any way to define the wanted sort order? (The only options I can find are ASC and DESC.)
I have tested this with Base and MySQL, internal HSQLDB and Firebird. Seems you are using Firebird, because it doesn’t use the right collation for sorting.
SELECT "Name" FROM "Table" ORDER BY "Name" COLLATE UNICODE ASC
You have to set the table to be executed directly in SQL. Without setting this it will show an error.
It should be possible to set the collation as property of the table as shown here:
http://www.ibexpert.net/ibe/index.php?n=Doc.CharacterSetsAndUnicodeInFirebird
I can’t test this at the moment, but I would prefer not to add collation to any query…
Example from above:
CREATE TABLE PERSONS (
PERS_ID INTEGER NOT NULL PRIMARY KEY,
LAST_NAME VARCHAR(50) COLLATE DE_DE,
FIRST_NAME VARCHAR(50) COLLATE DE_DE,
) ;
Thank you both, Wanderer and RobertG. The article on characters sets in Firebird is really informative. I am using Base as the front end to an SQLite database. This is a specimen query:
SELECT * FROM "T-Publications" WHERE "Publication code" LIKE 'P%' ORDER BY "Publication code" COLLATE UNICODE ASC
When I enter this into Base query design, it returns an unspecified syntax error. If I enter it directly as an SQL command, it returns the error “no such collation sequence: UNICODE (1)”.
If I use SQLite Studio to access the table and run the same query, the query runs but the ordering is not changed.
So I remain puzzled for the moment!
Sqlite is a library with different variants, but my guess is: Default Sqlite won’t include Unicode-tables:
https://www.sqlite.org/faq.html#q18
Read a bit on the train:
So it is possible, but the details can be complicated…