sort data in Ascending order POSTGRES SQL

Question

I have data in the format (Before sorting)

"8 - 15days"    "0"
"0 - 24hrs" "0"
"above 30days"  "0"
"2 - 7days" "0"
"16 - 30days"   "0"

I need to sort the data in order like this(After sorting)

"0 - 24hrs" "0"
"2 - 7days" "0"
"8 - 15days"    "0"
"16 - 30days"   "0"
"above 30days"  "0"

Can anyone help me to sort the data. I tried order by but it is not sorting the data in the way I need.

Laurenz Albe · Accepted Answer · 2020-07-17 11:02:27Z

1

If the column with the text in it is called x, you could use

ORDER BY x !~ '^[[:digit:]]',
         regexp_replace(x, '^([[:digit:]]*).*$', '0\1')::integer

answered Jul 17, 2020 at 11:02

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1