通过拆分点分隔字符串值对行进行排序(sort rows by split of dot

通过拆分点分隔字符串值对行进行排序(sort rows by split of dot-separated-string values)

我有一个索引列，字符串类型，其中数据的格式类似于“1.5.4.10.7”。

我需要进行一个查询返回排序的值，好像该列是通过在'。'上拆分字符串而得到的整数数组。 字符。

我可以用以下方法提取数组：

select string_to_array(index,'.') as index_array from performance_indicators;

我怎样才能1）将数组元素转换为整数，2）对整数数组进行排序？

I have an index column, string type, where the data is in format like "1.5.4.10.7".

I need to make a query returns the values sorted as if the column were an array of integers derived by splitting the string on the '.' character.

I can extract the array with:

select string_to_array(index,'.') as index_array from performance_indicators;

How can I 1) convert the array elements to integers, and 2) sort on the array of integers?

最满意答案

您可以简单地将字符串数组转换为整数数组并按值排序，例如：

with a_table(col) as ( values ('1.2.3.4'), ('10.2.3.4'), ('3.4.5.6') ) select string_to_array(col, '.')::int[] from a_table order by 1; string_to_array ----------------- {1,2,3,4} {3,4,5,6} {10,2,3,4} (3 rows)

You can simply cast the string array to integer array and sort by the value, e.g.:

with a_table(col) as ( values ('1.2.3.4'), ('10.2.3.4'), ('3.4.5.6') ) select string_to_array(col, '.')::int[] from a_table order by 1; string_to_array ----------------- {1,2,3,4} {3,4,5,6} {10,2,3,4} (3 rows)