sql - 如何 parse_date 不是英文的日期？

Question

我将以下格式的“日期”存储为字符串，请参见 2 个示例:

vrijdag 1 mei 2020,
donderdag 4 juni 2020

现在我想 date_parse 但问题是这不适用于荷兰语日期。我现在的解决方法是将第一部分(荷兰语日期名称)替换为 '' 并将荷兰语月份名称替换为英文版本。像这样:

SAFE.PARSE_DATE("%d %B %Y", REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
    REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(dutchdate,
     'maandag', ''), 'dinsdag', ''), 'woensdag', ''), 'donderdag', ''), 'vrijdag', ''
    ), 'zaterdag', ''), 'zondag', ''), 'januari','january'),'februari','february'),'maart','march'
    ),'mei','may'),'juni','june'),'juli','july'),'augustus','august'),'oktober','october'))

这对我来说看起来很愚蠢，所以我想知道是否有更好的方法来实现这一目标？就像其他一些 SQL 方言具有的语言参数一样？

Answer 1

考虑下面示例中的方法

#standardSQL
with `project.dataset.table` as (
  select 'vrijdag 1 mei 2020' dutchdate union all
  select 'donderdag 4 juni 2020' 
), months as (
  select 'januari' month_d,'january' month_e union all
  select 'februari','february' union all
  select 'maart','march' union all
  select 'mei','may' union all
  select 'juni','june' union all
  select 'juli','july' union all
  select 'augustus','august' union all
  select 'oktober','october' 
)
select dutchdate, 
  safe.parse_date("%d %B %Y",(
    select replace(dutchdate_without_day, month_d, month_e)
    from months 
    where regexp_contains(dutchdate_without_day, month_d)
  )) parsed_date
from `project.dataset.table`, 
unnest([struct(regexp_replace(dutchdate, r'^\w+ ', '') as dutchdate_without_day)])

有输出