regex - 在 PostgreSQL 9.3 中更改 varchar 列中的日期格式

Question

我有一个带有信息表的 PostgreSQL 数据库，其中包含有关人员的评论。

一个例子是 X 先生，1953 年 18 月 12 日出生在 Y...

我想使用独特的 PostgreSQL 请求或方法将日期格式更改为 ISO 8601 (YYYY-MM-DD)。

我知道如何检测数据库中的日期:'(\d{2})/(\d{2})/(\d{4})'。

使用此模式替换的最佳方法是什么？

我考虑过将 regex_replace 与拆分混合，但它似乎太复杂了，我相信有更简单的方法。

如果有人有好的建议，我会采纳!

谢谢

Answer 1

假设您的输入中有 XX/XX/XXXX 类似日期的子字符串，它始终是一个日期。然后，您可以使用的是单个 REGEXP_REPLACE，其模式包含捕获组以及替换模式中相应的反向引用，按要求的顺序排列并使用所需的分隔符:

REGEXP_REPLACE(s, '(\d{2})/(\d{2})/(\d{4})', '\3-\2-\1', 'g')

注意:如果您需要将这些字符串作为整个单词进行匹配，则可以使用单词边界 \y 将整个模式括起来，'\y(\d{2})/(\d{2})/(\d{4})\y'。 \d{2} 匹配两位数字，\d{4} 匹配四位数字。 (...) 创建一个编号的捕获组(从左到右从 ID 1 开始)，它们的内容用 \n 反向引用引用。 'g' 替换字符串中所有出现的地方。

安online test :

CREATE TABLE table1
    (s character varying)
;

INSERT INTO table1
    (s)
VALUES
    ('Mr X, born the 18/12/1953 in Y...'),
    ('Mr Y, died the 18/12/2053 in Y...')
;
select REGEXP_REPLACE(s, '\y(\d+)/(\d+)/(\d+)\y', '\3-\2-\1', 'g') as Results from table1;