[Fixed in 2018.1.1 + quick fix] Anomaly in German sorting
Since centuries the German language carries along a special character
that had no upper case representation. End o f June 2017 there was the
official decision that lower case ß $c(223) now also has an upper case
representation $c($zhex("1e9e")) looking pretty similar in most fonts.
[ISO defined this character already in 2008.]
Investigating $zconvert(...,"U") and $system.SQL.UPPER(),$system.SQL.SQLUPPER()
it turned out that $c(223) is unchanged during conversion.
No big issue for presentation as both "ß" look alike.
But bad for sorting.
With NLS correctly set to DEUW you get for lower case:
but for upper case it's broken:
Similar for global collation GERMAN3
Technically the sort is correct as lower case sorts after upper case.
The issue is already reported as prodlog 147715
I think you should be aware of this unexpected behavior for all those cases
where you don't have control over the content.
Eg. in text analysis or similar high volume input streams.
In past in typical properties this was often cheated using SS or SZ instead.
But this means changing the content and the length.
Switzerland just eliminated ß as valid character some years ago. Another approach.