Teradata中使用REGEXP_SUBSTR提取美元金额的技术求助

核心需求梳理 #

需要匹配两种美元金额格式：

• 美元符号在前：$后接数字（含数字间有空格的情况，需去除空格）
• 美元符号在后：数字后接$

问题分析 #

现有正则[\$]\K[0-9]+的局限性：

• 仅能匹配$后连续无空格的数字，无法处理数字间带空格的场景
• 完全不支持数字在前、$在后的格式

解决方案 #

1. 匹配$在前的金额（含数字间空格）

先匹配$后所有数字和空格的组合，再通过替换函数去除空格：

-- 提取$后数字并去除空格
SELECT REGEXP_REPLACE(REGEXP_SUBSTR('IND GEE & INTY FOR $800 000 XDAD CODFSDF', '\$[0-9 ]+', 1, 1, 'i'), ' ', '') AS amount;

正则说明：

• \$[0-9 ]+：匹配$开头，后续跟着一个或多个数字/空格的字符串
• REGEXP_REPLACE(..., ' ', '')：删除匹配结果里的所有空格，得到连续数字

2. 匹配数字在前、$在后的金额

先匹配数字+空格+$的组合，去掉空格和末尾$后，统一补上前缀$：

-- 提取数字在前的金额，整理格式
SELECT CONCAT('$', REGEXP_REPLACE(REGEXP_SUBSTR('IND GEE & INTY FOR 800$ XDAD CODFSDF', '[0-9 ]+\$', 1, 1, 'i'), ' |\$', '')) AS amount;

正则说明：

• [0-9 ]+\$：匹配一个或多个数字/空格结尾是$的字符串
• REGEXP_REPLACE(..., ' |\$', '')：去掉空格和末尾的$，再用CONCAT补上前缀$统一格式

3. 通用正则（覆盖两种格式）

用分支判断同时兼容两种格式，输出统一的$XXX格式：

-- 通用匹配两种格式，自动整理金额格式
SELECT 
  CASE 
    WHEN REGEXP_SUBSTR(input_str, '\$[0-9 ]+', 1, 1, 'i') IS NOT NULL THEN
      REGEXP_REPLACE(REGEXP_SUBSTR(input_str, '\$[0-9 ]+', 1, 1, 'i'), ' ', '')
    WHEN REGEXP_SUBSTR(input_str, '[0-9 ]+\$', 1, 1, 'i') IS NOT NULL THEN
      CONCAT('$', REGEXP_REPLACE(REGEXP_SUBSTR(input_str, '[0-9 ]+\$', 1, 1, 'i'), ' |\$', ''))
    ELSE NULL
  END AS amount
FROM (
  SELECT 'ABCD EG 6 MN INT $30870' AS input_str UNION ALL
  SELECT 'BAN000 JAN LTD TO ZZZ$55000' UNION ALL
  SELECT 'LTD TO $800000 sds' UNION ALL
  SELECT 'IND GEE & INTY FOR $800 000 XDAD CODFSDF' UNION ALL
  SELECT 'IND GEE & INTY FOR 800$ XDAD CODFSDF'
) AS test_data;

执行后输出结果：

• $30870
• $55000
• $800000
• $800000
• $800

关键注意点 #

• 正则里的$必须用\$转义，避免被当作行尾锚点
• 数字间的空格无法通过正则直接跳过，必须用替换函数去除
• 分支判断可以确保两种格式都被正确识别，输出统一的金额格式

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