sqldatabaseoracle-databasesortingsql-order-by

How to sort numbers before letters with linguistic sorting?

In an Oracle SQL query which uses binary sorting rules, numbers are sorted before letters. But if the query uses a language specific collation to enable linguistic sorting, then numbers are sorted after letters.
Example: https://dbfiddle.uk/BBfvb2lh

This differs from e.g. .NET sorting, where even with linguistic sorting the numbers are sorted before letters.
Example: https://dotnetfiddle.net/65G3na

How can I use linguistic sorting and sort numbers before letters in an Oracle SQL query?

Solution

  • I've got a simpler solution in my follow up question:
    The order is correct if I replace COLLATE XGERMAN_AI with COLLATE GENERIC_M_AI.

    Extended examples:
    Oracle: https://dbfiddle.uk/TOzViGhK
    .NET: https://dotnetfiddle.net/gytC9a