通过拆分点分隔字符串值对行进行排序(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)更多推荐
发布评论