使用dbt（Snowflake）提取文本中Mbps前首个数值的正则问题排查

我用dbt连接Snowflake做数据转换，表中PEAK_INFORMATION_RATE列包含文本与数值信息，需要提取Mbps前出现的首个数值，示例如下：

Example 1: '55Mbps downstream/11Mbps upstream' → 期望提取55
Example 2: '345 Mbps downstream/115 Mbps upstream' → 期望提取345
Example 3: '40Mbps Down / 3Mbps up' → 期望提取40
Example 4: '56 Mbps Download / 34 Mbps Upload' → 期望提取56

编写的dbt模型运行后返回-1，代码如下：

WITH rates_transformed AS (
    SELECT
        source_table,
TRY_CAST(
            CASE
                -- Extract numeric value directly before "Mbps" when it's followed by text/space and "/"
                WHEN REGEXP_LIKE(PEAK_INFORMATION_RATE, '\\d+\\s*Mbps[^/]+/') THEN
                    TRY_CAST(REGEXP_SUBSTR(PEAK_INFORMATION_RATE, '(\\d+)\\s*Mbps', 1, 1, 'e') AS FLOAT)

                ELSE -1
            END
        ) AS STD_val
FROM {{ ref('stg_model') }}
    )
    Select * from rates_transformed

错误原因分析 #

问题出在REGEXP_LIKE的匹配规则过于严格：

• 规则\\d+\\s*Mbps[^/]+/要求Mbps后必须先有非/字符，再紧跟/，但实际场景中（比如示例3）Mbps后是空格和Down再到/，或者如果字符串末尾没有/，这个规则直接不匹配，导致触发ELSE -1分支。
• 实际上我们只需要提取首个Mbps前的数值，完全不需要额外判断后续是否有/。

修正后的代码 #

WITH rates_transformed AS (
    SELECT
        source_table,
        -- 直接提取首个Mbps前的数值，无匹配时返回NULL
        TRY_CAST(REGEXP_SUBSTR(PEAK_INFORMATION_RATE, '(\\d+)\\s*Mbps', 1, 1, 'e') AS FLOAT) AS STD_val
    FROM {{ ref('stg_model') }}
)
SELECT * FROM rates_transformed

如果需要无匹配时返回-1（替代NULL），可以用COALESCE处理：

WITH rates_transformed AS (
    SELECT
        source_table,
        COALESCE(TRY_CAST(REGEXP_SUBSTR(PEAK_INFORMATION_RATE, '(\\d+)\\s*Mbps', 1, 1, 'e') AS FLOAT), -1) AS STD_val
    FROM {{ ref('stg_model') }}
)
SELECT * FROM rates_transformed

修正说明 #

1. 移除了多余的CASE WHEN和REGEXP_LIKE判断，简化逻辑
2. 正则(\\d+)\\s*Mbps可以匹配所有示例中「数字+可选空格+Mbps」的组合，配合'e'参数直接返回捕获到的纯数字
3. TRY_CAST确保转换失败时返回NULL，避免报错

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