Search code examples
sqldatabaseoraclesortingsql-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

  • Assuming you can accept sorting numbers first, followed by everything else second, then you could use the following logic:

    SELECT val
FROM yourTable
ORDER BY
    CASE WHEN REGEXP_LIKE(val, '^[0-9]') THEN 1 ELSE 2 END,  -- numbers first
    val;                                                     -- then sort by val