Ask Your Question
0

Sorting in Base with german Umlaute

asked 2019-04-22 13:34:04 +0100

FraKa gravatar image

updated 2019-04-22 15:51:25 +0100

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?

edit retag flag offensive close merge delete

Comments

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

Cookievore gravatar imageCookievore ( 2019-04-22 17:41:37 +0100 )edit

2 Answers

Sort by » oldest newest most voted
1

answered 2019-04-22 22:42:35 +0100

Ratslinger gravatar image

updated 2019-04-25 22:35:25 +0100

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 @m.a.riosv

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

edit flag offensive delete link more

Comments

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...

FraKa gravatar imageFraKa ( 2019-04-25 21:54:32 +0100 )edit

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.

Ratslinger gravatar imageRatslinger ( 2019-04-25 22:39:22 +0100 )edit

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!

FraKa gravatar imageFraKa ( 2019-04-29 22:20:31 +0100 )edit
0

answered 2019-04-22 22:50:08 +0100

m.a.riosv gravatar image

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

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2019-04-22 13:34:04 +0100

Seen: 90 times

Last updated: Apr 25