从PostgreSQL对记录进行排序,带有多个小数点(.) [英] Sorting records from PostgreSQL with multiple decimal points (.)
问题描述
我在PostgreSQL中有一个记录列表,如下所示,这些实际上是各本书的各个部分 记录以以下格式生成.
I have a list of records in PostgreSQL as follows, these are actually sections of various books The records are generated in the below format.
1
7.1
6.2
7.1
7.4
6.8.3
6.8.2
10
1.1
7.6
6.1
11
8.3
8.5
1.1.2
6.4
6.6
8.4
1.1.6
6.8.1
7.7.1
7.5
7.3
我要这样排序
1
1.1
1.1.2
1.1.6
6.2
6.4
6.5
6.6
6.7
6.8.1
6.8.2
6.8.3
7.2
7.3
7.4
7.5
7.6
7.7.1
7.7.2
8.3
8.4
8.5
10
这是一个varchar列,因此我尝试使用此处列出的内容. 从Oracle中排序带有多个小数点(.)的记录
It's a varchar column so i have tried using what listed here. Sorting records from Oracle with multiple decimal points (.)
select * from tbl_wo_kitting where wo_project_id = 1000033
ORDER BY to_number(regexp_substr(line_no, '[^.]+', 1, 1)) NULLS FIRST
它一直说无效的函数名称regexp_substr. 这样排序的功能是什么?
It keeps saying invalid function name regexp_substr. What are the functions to sort that way?
非常感谢您的帮助.
推荐答案
您可以通过将字符串转换为整数数组,然后对数组进行排序来实现:
You can do this by converting the string to an integer array, then sort on the array:
select *
from tbl_wo_kitting
where wo_project_id = 1000033
ORDER BY (string_to_array(line_no, '.'))::int[]
请注意,如果line_no_
列中的值无法转换为数字,则此操作将失败.
Note that this will fail if there are values that cannot be converted to numbers in the line_no_
column.
这篇关于从PostgreSQL对记录进行排序,带有多个小数点(.)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!