如何查看Redshift（PostgreSQL）regexp_substr输出的长度？

原始查询与数据示例 #

查询语句 #

SELECT
    data
    ,regexp_substr(data,'bottle~([[:alnum:]]{32})', 1, 1, 'ep') as bval
    ,len(bval) as WhyIsntThisAlways32or0
FROM activity
WHERE TRUE
;

数据记录示例 #

bottle~6c3f28d9f93e9000119cd9063d07d115~984983

问题现象 #

查询能正确提取bval为6c3f28d9f93e9000119cd9063d07d115，但WhyIsntThisAlways32or0列始终返回0或1，无法显示子串实际长度。

原因分析 #

核心问题是SQL查询的执行顺序限制：

• SQL按 FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY 的顺序处理逻辑，SELECT子句中定义的列别名，只能在后续的ORDER BY中引用，无法在同一条SELECT的其他列表达式中直接使用。
• 你写的len(bval)中，bval并没有指向前面提取的别名，而是被数据库解析为activity表中的列（如果表中无此列，则返回NULL）。不同数据库对len(NULL)的处理或隐式转换规则不同，导致返回0或1这类异常值。

修复方案 #

方案1：使用子查询/CTE嵌套（推荐） #

通过子查询或CTE先完成bval的提取，再在外层查询计算其长度，这样就能正确引用已生成的bval列：

子查询写法

SELECT
    data,
    bval,
    len(bval) as WhyIsntThisAlways32or0
FROM (
    SELECT
        data,
        regexp_substr(data,'bottle~([[:alnum:]]{32})', 1, 1, 'ep') as bval
    FROM activity
) sub_query
WHERE TRUE;

CTE写法

WITH activity_with_bval AS (
    SELECT
        data,
        regexp_substr(data,'bottle~([[:alnum:]]{32})', 1, 1, 'ep') as bval
    FROM activity
)
SELECT
    data,
    bval,
    len(bval) as WhyIsntThisAlways32or0
FROM activity_with_bval
WHERE TRUE;

方案2：重复regexp_substr表达式（不推荐） #

如果场景简单，也可以直接重复提取逻辑，但这种方法会增加代码冗余，不利于后续维护：

SELECT
    data,
    regexp_substr(data,'bottle~([[:alnum:]]{32})', 1, 1, 'ep') as bval,
    len(regexp_substr(data,'bottle~([[:alnum:]]{32})', 1, 1, 'ep')) as WhyIsntThisAlways32or0
FROM activity
WHERE TRUE;

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