Postgresql ORDER BY spaces

Question

假设我有一个表，其中有一列，即：

5 absolute
5.0
5.1
last
50
5 elite
edge

我需要将其排序为(使用postgresql方法)：

5 absolute
5 elite
5.0
5.1
50
edge
last

但是如果我使用经典的"ORDER BY column ASC“，我会得到：

50
5.0
5.1
5 absolute
5 elite
edge
last

有很多像substring或using这样的工具，但我不能理解它们是如何工作的。

我需要做什么？

Answer 1

这应该可以做到：

order by regexp_replace(the_column, '[^0-9\.]', '', 'g')::numeric DESC

它从值中删除所有非数字的字符(只留下数字和.)，然后将其转换为数字。然后使用该数字进行降序排序。

唯一的问题是5.0将在上面的5.1之后排序。

如果您需要考虑没有任何数字的值，则如下所示：

order by 
      case 
         when regexp_replace(the_column, '[^0-9\.]', '', 'g') <> 
            then regexp_replace(the_column, '[^0-9\.]', '', 'g')::numeric
      end DESC NULLS LAST, 
      the_column DESC -- to sort the "NULL" values alphabetically

如果你不想重复正则表达式，你可以这样做：

with clean_data as (
   select the_column, 
          regexp_replace(the_column, '[^0-9\.]', '', 'g') as clean_column, 
          ....
   from ...
)
select *
from clean_data
order by case 
           when clean_column <> '' then clean_column::numeric 
         end desc nulls last, 
         the_column desc;