Sort varchar by its numeric fields

2 votes

3 answers

2590 views

postgresql order-by postgresql-12 natural-sort

                          *I'm sure this has been asked before, but since the question can be formulated in many different ways it's difficult to find the proper answer.*

I have an **orders** table with a *varchar* field for the order **number**, which is formatted with 4-digits year, a dash (-), and a progressive numeric value. For example it may contain the following values:

`SELECT number FROM orders ORDER BY number LIMIT 10;`

      number  
    ----------
     1999-13
     2019-11
     2020-1
     2020-10
     2020-100
     2020-12
     2020-2
     2020-21
     2020-3
     2021-1

I need to sort that field by year and then by the progressive number, with this expected result:

      number  
    ----------
     1999-13
     2019-11
     2020-1
     2020-2
     2020-3
     2020-10
     2020-12
     2020-21
     2020-100
     2021-1

A DB fiddle is here .

My questions are:
1) Which is the simplest way to achieve this with an ORDER subclause?
2) How to add an efficient index using this custom sort without having to modify the table?

I would like to keep at least the first answer as database-agnostic as possible (that's why I haven't included db specific tags), but if different best answers are possible for different DBMS/versions, let's assume PostgreSQL 12.
                        

Asked by Claudio Floreani (102 rep)

Nov 20, 2020, 12:15 PM
Last activity: Nov 20, 2020, 02:45 PM

Sort varchar by its numeric fields

Related Questions