Sorting in Base with german Umlaute

[FraKa]

LO 6.2.3, Win 10, german version
There are 2 issues with sorting in Base under new firebird embedded.
The issues are related to all parts of base: tables, queries, forms and reports.
It´s after I converted a database from HSQLDB to firebird.

1. Now sorting with german “Umlaute” (ä,ö,ü) in textfields is not regular on my system under firebird (it´s different to HSQLDB).
   Sorting example:

HSQLDB: a ä b c o ö z

Firebird : a b c o z ä ö

Firebird always sorts the Umlaute to the end of the list (incorrect), HSQLDB ist correct.

2. Sorting of capital letters:
   Firebird always sorts uppercases before lowercases, HSQLDB ignores this and sorts both types identical
   Example:

HSQLDB: a b N M r s

Firebird : N M a b r s

Is there a way to let firebird sort like HSQLDB?

[Cookievore]

Interesting question. I’d really like to upvote your request but it is not possible with karma of 1.

[Ratslinger]

Hello,

Have not been able to change table sorting. Best to file a bug report → Bugzilla

Can offer this for use in a Query (Run SQL command directly must be on) :

SELECT * FROM "TABLE_NAME" ORDER BY "FIELD_NAME" collate UNICODE asc

Note: asc can also be desc.

Edit 2019-04-25:

@FraKa - don’t understand your problem so have attached a sample. This sample incorporates my answer & that from @mariosv

There is one table with three fields - ID, Item, NEW_COLL

NEW_COL has ‘collate’ applied from creation. This is the link provided in the other answer. Query1 demos its use and needs nothing special for proper sequencing.

Item is just a created field. Using Query2, which contains the collate keyword, will demo its use.

Sample ------ SortTest.odb

[FraKa]

Thank you, Ratslinger! Followed your easy and clear instructions. But… the problem is, that in SQL view COLLATE wouldn´t be recognized as an valid keyword. Normaly a valid keyword ist recognized by changing the font color after the keyword is completly typed in. After typing COLLATE, no change of the color. Closing the window results in an error “Invalid SQL Statement”.
Sorry, need further help…

[Ratslinger]

Please see edit in my answer.

Is it possible you did not turn on Run SQL command directly?

Are you working with Firebird Base file?

Edit: Have also used the Query2 SQL to create a View and it also runs in Tools->SQL... with no problems in either.

[FraKa]

Thanks a lot Ratslinger! Always used design mode to create and modify my queries. “Edit in SQL view” was new to me, also “Run SQL command directly”. So I converted my existing queries from design mode to SQL and was finally able to add the “collate unicode” command. Now everything works fine as expected. Realy a good lesson.
I appreciate your help!

[mariosv]

Please take a look on Firebird Character Sets and Collations, I think it could be helpful in this case.