Snowflake-SQL: Convert Data from month name in different language

user_18459823 0
Viewed 379

To convert a date-string that contains the name of the month in a data-field I could use

select to_date('01October2022', 'DDMMMMYYYY')

..I didn't find a way to convert the string if the month name is not english e.g. in german like 01Oktober2022

I'm aware of this solution:

create  or replace function monthname_2_value(date_value string)
  returns string
  as
  $$
    case
        when  lower(date_value) like '%januar%'     then replace(lower(date_value), 'januar',      '01')
        when  lower(date_value) like '%feburar%'    then replace(lower(date_value), 'feburar',     '02')
        when  lower(date_value) like '%märz%'       then replace(lower(date_value), 'märz',        '03')
        when  lower(date_value) like '%april%'      then replace(lower(date_value), 'april',       '04')
        when  lower(date_value) like '%mai%'        then replace(lower(date_value), 'mai',         '05')
        when  lower(date_value) like '%juni%'       then replace(lower(date_value), 'juni',        '06')
        when  lower(date_value) like '%juli%'       then replace(lower(date_value), 'juli',        '07')
        when  lower(date_value) like '%august%'     then replace(lower(date_value), 'august',      '08')
        when  lower(date_value) like '%september%'  then replace(lower(date_value), 'september',   '09')
        when  lower(date_value) like '%oktober%'    then replace(lower(date_value), 'oktober',     '10')
        when  lower(date_value) like '%november%'   then replace(lower(date_value), 'november',    '11')
        when  lower(date_value) like '%dezember%'   then replace(lower(date_value), 'dezember',    '12')
    end
  $$
  ;

select to_date(monthname_2_value('01Oktober2022'), 'DDMMYYYY')

snowflake-cloud-data-platform sql
1 Answers

A shorter SQL UDF to replace the month name with its number:

create or replace function replace_month(x string)
returns string
as $$
select regexp_replace(x, '[^0-9]+', o[regexp_substr(lower(x), '[^0-9]+')])
from (select object_construct('märz','03', 'oktober','10', 'november','11', 'dezember', 12) o)
$$
;

You can use it like this:

select replace_month('01März2022');

-- 01032022
user_1324380
Related