如何在AWS Redshift中用正则提取两个子串之间的指定数值

问题原因 #

• 第一个报错核心原因是AWS Redshift没有内置regexp_substring函数，提取正则匹配子串的正确函数名为REGEXP_SUBSTR
• 第二种写法除可能存在的函数名拼写问题外，用到的正则正向零宽断言不符合Redshift的POSIX正则引擎语法要求，即使修正函数名也无法正常执行

方案1：JSON解析法（推荐） #

你存储的字符串本身是类JSON数组结构，仅需要替换单引号为标准JSON要求的双引号，即可直接用Redshift内置JSON函数提取目标值，容错率远高于正则，不会受字段顺序、空格变化影响：

SELECT 
  order_id,
  discount_codes,
  CAST(
    JSON_EXTRACT_PATH_TEXT(REPLACE(discount_codes, '''', '"'), '0', 'amount') 
    AS FLOAT
  ) AS amount
FROM orders_shopify_de;

函数逻辑说明：

• REPLACE(discount_codes, '''', '"')：把原字符串里的单引号替换为双引号，转为标准JSON格式
• JSON_EXTRACT_PATH_TEXT(..., '0', 'amount')：提取JSON数组下标为0的元素中amount字段的文本值
• CAST(...) AS FLOAT：把提取到的字符串格式数值转为浮点类型

方案2：正则提取法 #

如果需要用正则实现，使用Redshift支持的REGEXP_SUBSTR函数配合捕获组语法即可：

SELECT 
  order_id,
  discount_codes,
  CAST(
    REGEXP_SUBSTR(discount_codes, '''amount'': ''([0-9]+\.?[0-9]*)''', 1, 1, 'e')
    AS FLOAT
  ) AS amount
FROM orders_shopify_de;

函数逻辑说明：

• 正则'amount': '([0-9]+\.?[0-9]*)'匹配amount字段对应的单引号包裹的数值内容，捕获组1即为纯数值
• 最后一个参数'e'表示开启捕获组提取模式，直接返回第一个捕获组的内容

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