Snowflake数据库用SQL正则提取redeemable_properties值的需求

假设你的表名为your_table，存储目标字符串的列名为properties_str，可以通过以下SQL语句提取redeemable_properties对应的数值字符串并去除单双引号：

1. 查询验证结果 #

SELECT
  properties_str,
  -- 先匹配捕获redeemable_properties后的引号内内容，再移除所有单双引号
  REGEXP_REPLACE(
    REGEXP_SUBSTR(properties_str, ':redeemable_properties:\\s*[\'\"](.*?)[\'\"]', 1, 1, 's', 1),
    '[''"]', ''
  ) AS redeemable_properties_clean
FROM your_table;

语句说明： #

• REGEXP_SUBSTR：匹配多行字符串中:redeemable_properties:后的内容，\s*匹配任意空白字符，[\'"]匹配单/双引号，(.*?)非贪婪捕获引号内的所有内容，'s'参数允许.匹配换行符（适配原字符串的多行格式），最后一个1表示返回第一个捕获组的内容。
• REGEXP_REPLACE：将捕获到的内容里的单引号、双引号全部移除。

2. 添加并更新新列 #

如果需要将清理后的值永久存入新列，执行以下语句：

-- 添加新列
ALTER TABLE your_table
ADD COLUMN redeemable_properties_clean STRING;

-- 更新新列值
UPDATE your_table
SET redeemable_properties_clean = REGEXP_REPLACE(
    REGEXP_SUBSTR(properties_str, ':redeemable_properties:\\s*[\'\"](.*?)[\'\"]', 1, 1, 's', 1),
    '[''"]', ''
);

这个方案能处理redeemable_properties值为空的情况（比如类似例子中redemption_meta_keys的空值场景），会返回空字符串。

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