Teradata中使用正则提取并拆分不同字段内的费用金额需求

原始Teradata表 #

目标结果表 #

核心需求 #

• 将分散在COLUMN_1/COLUMN_2/COLUMN_3中的各类费用拆分到对应字段
• 针对OD FEE场景，需提取最终金额，忽略中间计算数值（如示例中OD FEE 2 * $4 $8只取$8对应的数值）

Teradata实现SQL #

WITH combined_fees AS (
    SELECT 
        ID,
        COLUMN_1 || ' ' || COLUMN_2 || ' ' || COLUMN_3 AS all_fee_text
    FROM your_table_name
)
SELECT 
    ID,
    COALESCE(REGEXP_SUBSTR(all_fee_text, 'FEE \\$\\d+\\.*'), '$0') AS FEE,
    COALESCE(REGEXP_SUBSTR(all_fee_text, 'UNPAID FEE \\$\\d+\\.*'), '$0.') AS UNPAID_FEE,
    COALESCE(REGEXP_REPLACE(REGEXP_SUBSTR(all_fee_text, 'ACCOUNT_FEE \\$\\d+\\.*'), 'ACCOUNT_FEE \\$', ''), '0') AS ACCOUNT_FEE,
    COALESCE(REGEXP_REPLACE(REGEXP_SUBSTR(all_fee_text, 'OD FEE .*?(\\$\\d+)', 1, 1, 'i', 1), '\\$', ''), '0') AS OD_FEE
FROM combined_fees;

代码说明 #

1. 合并字段：将三个列的内容合并为一个字符串，简化跨列匹配逻辑
2. 正则匹配规则：
   • FEE：匹配FEE $后跟数字及可选小数点，无匹配则返回$0
   • UNPAID_FEE：匹配UNPAID FEE $后跟数字及可选小数点，无匹配则返回$0.
   • ACCOUNT_FEE：匹配后移除前缀和$符号，无匹配则返回0
   • OD_FEE：通过捕获组提取OD FEE后的最后一个金额值，移除$符号，无匹配则返回0
3. 空值处理：用COALESCE确保未匹配到费用时返回符合要求的默认值

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