如何在Vertical SQL中从自由文本字符串提取所需日期？

适配场景说明 #

针对你提到的Note自由文本字段存储的M/D/YY格式日期，可通过Vertica内置正则函数实现提取需求，无需额外工具。

方案一：提取全部日期并空格分隔存储 #

操作步骤 #

1. 新增存储全量日期的字段

ALTER TABLE 你的业务表名 ADD COLUMN all_extracted_dates VARCHAR(200);

2. 批量填充字段值

UPDATE 你的业务表名
SET all_extracted_dates = (
    SELECT STRING_AGG(date_match, ' ')
    FROM REGEXP_MATCHES(Note, '\b\d{1,2}/\d{1,2}/\d{2}\b', 'g') AS t(date_match)
);

正则规则说明 #

\b\d{1,2}/\d{1,2}/\d{2}\b 限定匹配1-2位月份、1-2位日期、2位年份的组合，单词边界避免匹配到其他带斜杠的非日期数字。

方案二：提取最后一个日期存储 #

操作步骤 #

1. 新增存储末次日期的字段，建议直接存DATE类型方便后续日期计算，也可按需存字符串类型

ALTER TABLE 你的业务表名 ADD COLUMN last_extracted_date DATE;

2. 批量填充字段值

UPDATE 你的业务表名
SET last_extracted_date = TO_DATE(
    REGEXP_SUBSTR(
        Note,
        '\b\d{1,2}/\d{1,2}/\d{2}\b',
        1,
        REGEXP_COUNT(Note, '\b\d{1,2}/\d{1,2}/\d{2}\b')
    ),
    'MM/DD/YY'
);

逻辑说明 #

先通过REGEXP_COUNT统计字段中符合格式的日期总数，再通过REGEXP_SUBSTR取最后一个匹配结果，转成日期类型存储即可，若只需存字符串直接去掉TO_DATE转换逻辑。

效果验证 #

和你给出的示例完全匹配：

• 文本1st order on 3/2/21, second 5/5/21：全量日期返回3/2/21 5/5/21，末次日期返回5/5/21
• 文本first delivery 2/2/21 second one 8/30/21：全量日期返回2/2/21 8/30/21，末次日期返回8/30/21
• 文本reported 1st: 2/2/21.：两个字段均返回2/2/21

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