如何在SQL中将字符串内的数字数组转换为整数数组

问题分析 #

你手里的数组是包含单个带末尾逗号的字符串的数组（比如ARRAY['1,2,3,']），直接用string_to_array会得到带空字符串的数组（['1','2','3','']），转整数时会报错，而且你没先取出数组里的字符串做预处理。

具体实现 #

以下是几种可行的SQL写法：

方法1：先清理末尾逗号再拆分聚合

-- 假设你的数组变量/列名为input_arr
SELECT array_agg(elem::INT) AS int_array
FROM (
    -- 取出数组中的字符串，清理末尾的逗号
    SELECT trim(trailing ',' FROM unnest(input_arr)) AS cleaned_str
) t,
-- 拆分清理后的字符串
unnest(string_to_array(t.cleaned_str, ',')) AS elem

方法2：用正则拆分自动忽略空值

如果字符串里可能有空格或多余逗号，用正则拆分更稳妥：

SELECT array_agg(elem::INT) AS int_array
FROM unnest(ARRAY['1,2,3,']) AS str,
-- 用正则匹配逗号（可带空格）拆分，自动过滤空内容
unnest(regexp_split_to_array(str, '\s*,\s*')) AS elem
WHERE elem <> ''

方法3：处理单个字符串的简化写法

如果是直接处理单个字符串（比如'1,2,3,'）而非数组，可以这样写：

SELECT array_agg(elem::INT)
FROM unnest(string_to_array(trim(trailing ',' FROM '1,2,3,'), ',')) AS elem

关键步骤说明 #

1. unnest(input_arr)：把数组中的单个字符串提取出来；
2. trim(trailing ',' FROM ...)：去掉字符串末尾多余的逗号；
3. string_to_array/regexp_split_to_array：把字符串拆分为单个数字字符串的数组；
4. array_agg(elem::INT)：把拆分后的字符串转成整数，再聚合为整数数组。

内容的提问来源于stack exchange，提问作者Klaus