0

I have theses values in my table

id | NAME
1 | 20 MEGA
2 | 30 MEGA
3 | 10 MEGA
4 | 300 MEGA
5 | 100 MEGA
6 | 25 MEGA

What i want:

id | NAME
3 | 10 MEGA
1 | 20 MEGA
6 | 25 MEGA
2 | 30 MEGA
5 | 100 MEGA
4 | 300 MEGA

I tried this query:

select * from table_name ORDER BY "name" ASC

But returns 

3   10 MEGA
5   100 MEGA
1   20 MEGA
6   25 MEGA
2   30 MEGA
4   300 MEGA

How can i do this?

mu is too short
  • 426,620
  • 70
  • 833
  • 800
Eduardorph
  • 153
  • 10
  • If you have only the 'MEGA' suffix you can just remove it and cast to integer the order by expression. But... what if you have '1 GIGA' too? – mauro Jun 21 '17 at 17:29
  • 2
    if `10 MEGA` is something that can be semantically translated into `10` + `MEGA` then maybe the two needs to be in two separate columns. In which case you can write efficient queries. – andreim Jun 21 '17 at 17:33
  • `order by split_part("name", ' ', 1)::int, split_part("name", ' ', 2)` – Abelisto Jun 21 '17 at 17:41
  • 2
    You can find other duplicates under the [`[natural-sort] [postgresql]` tags](https://stackoverflow.com/questions/tagged/natural-sort+postgresql). – mu is too short Jun 21 '17 at 18:02

1 Answers1

2

You should be able to ORDER BY the numbers specifically in the "name" column by treating them as an int. To do so, you'll want to ensure any non-digits are removed first using regexp_replace.

ORDER BY regexp_replace("name", '\D', '', 'g')::int ASC

You could take it a step further and convert empty values to 0 or NULL.

coyote
  • 61
  • 3