我在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对记录进行排序,带有多个小数点(.)
发布评论