Search code examples
sqlpostgresqlrails-postgresqlnatural-sort

Sort results in varchar column with Postgres

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?

Solution

  • 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.