BigQuery中提取格式不一致的STRING类型日期列的年份

针对你遇到的格式混乱的字符串日期列，这里提供几个实用的BigQuery SQL方案，避免转换报错的同时提取年份：

方法1：尝试多种常见日期格式转换 #

如果你的日期字段包含几种常见格式（如MM/DD/YYYY、DD-MM-YYYY、YYYY-MM-DD、Jan 01, 2023），用SAFE.PARSE_DATE结合COALESCE依次尝试转换，失败时返回NULL，不会触发报错：

SELECT
  Quality_Review___Dates_of_Review,
  EXTRACT(YEAR FROM COALESCE(
    SAFE.PARSE_DATE('%m/%d/%Y', Quality_Review___Dates_of_Review),
    SAFE.PARSE_DATE('%d-%m-%Y', Quality_Review___Dates_of_Review),
    SAFE.PARSE_DATE('%Y-%m-%d', Quality_Review___Dates_of_Review),
    SAFE.PARSE_DATE('%b %d, %Y', Quality_Review___Dates_of_Review)
  )) AS review_year
FROM
  `你的项目ID.你的数据集ID.你的表名`

你可以根据实际存在的日期格式，添加或删除SAFE.PARSE_DATE的分支。

方法2：正则提取四位数字年份 #

如果日期格式极度混乱，但年份都是四位数字（如2021、2024），直接用正则匹配独立的四位数字：

SELECT
  Quality_Review___Dates_of_Review,
  SAFE_CAST(REGEXP_EXTRACT(Quality_Review___Dates_of_Review, r'\b\d{4}\b') AS INT64) AS review_year
FROM
  `你的项目ID.你的数据集ID.你的表名`

r'\b\d{4}\b'确保匹配的是独立的四位数字，不会误取长数字串中的片段；SAFE_CAST在没有匹配到年份时返回NULL，避免报错。

方法3：处理两位年份的特殊情况（可选） #

如果字段中存在两位年份（如05/10/23），可以通过逻辑转换为四位年份：

SELECT
  Quality_Review___Dates_of_Review,
  CASE
    -- 优先匹配四位年份
    WHEN REGEXP_CONTAINS(Quality_Review___Dates_of_Review, r'\b\d{4}\b') THEN
      SAFE_CAST(REGEXP_EXTRACT(Quality_Review___Dates_of_Review, r'\b\d{4}\b') AS INT64)
    -- 处理两位年份，这里假设>50的是19xx，否则是20xx，可按需调整
    WHEN REGEXP_CONTAINS(Quality_Review___Dates_of_Review, r'\b\d{2}\b') THEN
      SAFE_CAST(
        CASE
          WHEN CAST(REGEXP_EXTRACT(Quality_Review___Dates_of_Review, r'\b\d{2}\b') AS INT64) > 50 THEN '19' || REGEXP_EXTRACT(Quality_Review___Dates_of_Review, r'\b\d{2}\b')
          ELSE '20' || REGEXP_EXTRACT(Quality_Review___Dates_of_Review, r'\b\d{2}\b')
        END AS INT64
      )
    ELSE NULL
  END AS review_year
FROM
  `你的项目ID.你的数据集ID.你的表名`

补充提示 #

• 可以用WHERE review_year IS NULL筛选出无法识别的脏数据，单独处理或标记；
• 如果需要给NULL值设置默认年份，用IFNULL(review_year, 0)（0替换为你需要的默认值）。

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