如何在Redshift SQL中统计首个数字前的逗号总数

解决方案 #

可以通过Redshift支持的正则表达式函数与字符串处理函数组合实现需求，具体SQL如下：

WITH sample_data AS (
    SELECT '[,,4,]' AS column1 UNION ALL
    SELECT '[2,,,]' AS column1
)
SELECT
    column1,
    -- 计算首个数字前的逗号数量
    LENGTH(LEFT(TRIM(column1, '[]'), REGEXP_INSTR(TRIM(column1, '[]'), '[0-9]', 1, 1) - 1)) 
    - LENGTH(REPLACE(LEFT(TRIM(column1, '[]'), REGEXP_INSTR(TRIM(column1, '[]'), '[0-9]', 1, 1) - 1), ',', '')) AS desired_output,
    -- 提取首个数字
    REGEXP_SUBSTR(TRIM(column1, '[]'), '[0-9]', 1, 1)::INT AS digit
FROM sample_data;

逻辑说明 #

1. 去除首尾方括号：用TRIM(column1, '[]')剥离原始字符串的[和]，例如将[,,4,]转为,,4,。
2. 定位首个数字位置：REGEXP_INSTR(..., '[0-9]', 1, 1)返回字符串中第一个数字的起始位置（从1开始计数）。
3. 统计逗号数量：
   • 用LEFT(...)截取首个数字之前的子串
   • 通过LENGTH(原串) - LENGTH(替换逗号后的串)计算逗号个数，替换逗号后的长度差即为逗号的数量
4. 提取首个数字：REGEXP_SUBSTR(..., '[0-9]', 1, 1)匹配第一个数字字符，再通过::INT转为整数类型。

测试结果 #

执行上述SQL后，将得到与示例一致的结果：

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