sql - REGEX 日期匹配格式

Question

我目前有一个包含不同日期条目(和字符串条目的混合)的数据集，我需要对其进行解析。有几个:'M/DD/YY', 'M/D/YY', 'MM/DD/YY', 'MM/D/YY', 'MM/DD/YYYY'...)。我可以使用一些支持来改进我的正则表达式来处理日期字段中输入的不同格式和可能的文本。

我当前的 Postgres 查询将其他条目分解到另一列中并重新格式化日期。尽管我已将年份增加到 4 位数字而不是 2 位数字，但我认为问题可能存在于 'YYYY-MM-DD' 格式中的某个地方，或者我的查询无法正确适应其他格式.

CASE WHEN date ~ '^\\\\d{1,2}/\\\\d{1,2}/\\\\d{4}$' THEN TO_DATE(date::date, 'YYYY-MM-DD')
     ELSE NULL END AS x_date,

CASE WHEN NOT date ~ '^\\\\d{1,2}/\\\\d{1,2}/\\\\d{4}$' AND date <> '' THEN date
     ELSE NULL END AS x_date_text

对于各种日期格式，应相应地重新格式化，对于其他非日期值，应将其移至另一列。

Answer 1

根据您的格式列表，我认为只需两个正则表达式就足以检查值:

• '^[0-9]{1,2}/[0-9]{1,2}/[0-9]{4}/$' 将映射到日期格式'MM/DD/YYYY'
• '^[0-9]{1,2}/[0-9]{1,2}/[0-9]{2}/$' 将映射到 '月/日/年'

您可以使用 CASE 构造根据正则表达式检查值，并在使用 TO_DATE() 时应用正确的掩码。

但是，由于您需要将数据拆分为两列，因此您需要繁琐地重复 CASE 表达式两次，每列一次。

简化解决方案(并使其在以后更易于维护)的一种方法是使用 CTE 列出正则表达式和关联的日期格式。您可以使用表LEFT JOIN CTE。

考虑以下查询:

WITH vars AS (
    SELECT '^[0-9]{1,2}/[0-9]{1,2}/[0-9]{4}/$' reg, 'MM/DD/YYYY' format
    UNION ALL '^[0-9]{1,2}/[0-9]{1,2}/[0-9]{2}/$', 'MM/DD/YY'
)
SELECT 
    CASE WHEN vars.reg IS NOT NULL THEN TO_DATE(t.date, vars.format) END x_date,
    CASE WHEN vars.reg IS NULL THEN t.date  END x_date_text
FROM 
    mytable t
    LEFT JOIN vars ON t.date ~ vars.reg

如果需要更多正则表达式/格式对，您只需扩展 CTE。请注意正则表达式应该是互斥的(即两个不同的正则表达式不应该匹配一个值)，否则你会在结果中得到重复的记录。