背景
在项目中有时候需要对时间日期字段进行规范化,比如业务时间各种各样格式的都有,甚至有部分存在不是日期时间格式,需要规范化字段和剔除掉不可用的脏数据;
思路
利用case when先将能匹配到的格式数据进行格式化,无法进行格式化的数据统一赋值为数据格式不规范;
代码
case
-- yyyymm
when regexp_like(date_field,'^[0-9]{6}$') then to_char(to_date(date_field,'yyyyMM'),'yyyy-MM')
-- yyyy
when regexp_like(date_field,'^[0-9]{4}$') then to_char(to_date(date_field,'yyyy'),'yyyy')
-- yyyy-M
when REGEXP_LIKE(date_field, '^[0-9]{4}-[1-9]{1}$') then to_char(to_date(date_field,'yyyy-MM'),'yyyy-MM')
-- yyyy-MM
when REGEXP_LIKE(date_field, '^[0-9]{4}-[0-9]{2}$') then to_char(to_date(date_field,'yyyy-MM'),'yyyy-MM')
-- yyyy年MM月
when REGEXP_LIKE(date_field, '^[0-9]{4}年[0-9]{1,2}月$') then to_char(to_date(date_field,'yyyy年MM月'),'yyyy-MM')
-- yyyy-MM-dd
when REGEXP_LIKE(date_field, '^[0-9]{4}-[0-9]{2}-[0-9]{2}$') then date_field
-- yyyy
when REGEXP_LIKE(date_field, '^[0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}:[0-9]{2}$') then date_field
-- yyyy-mm-dd hh24:mi:ss
when REGEXP_LIKE(date_field, '^[0-9]{4}/[0-9]{1,2}/[0-9]{1,2} [0-9]{1,2}:[0-9]{1,2}$') then TO_CHAR(TO_DATE(date_field, 'yyyy/mm/dd hh24:mi'), 'yyyy-mm-dd hh24:mi:ss')
-- yyyy-mm-dd hh24:mi
when REGEXP_LIKE(date_field, '^[0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}$') then date_field
-- mm/dd/yy
when REGEXP_LIKE(date_field, '^\d{1,2}/\d{1,2}/\d{2}$') then TO_CHAR(TO_DATE(date_field, 'mm/dd/yy'), 'yyyy-mm-dd')
-- YYYY-MM-DD HH24:MI:SS.MS
when REGEXP_LIKE(date_field, '^[0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}:[0-9]{2}\.[0-9]{1,6}') then TO_CHAR(TO_TIMESTAMP(substring(date_field,1,19), 'YYYY-MM-DD HH24:MI:SS.MS'), 'yyyy-MM-dd hh24:mi:ss')
-- YYYY-MM-DDTHH24:MI:SS.MS
when REGEXP_LIKE(date_field, '^[0-9]{4}-[0-9]{2}-[0-9]{2}T[0-9]{2}:[0-9]{2}:[0-9]{2}\.[0-9]{3}\+[0-9]{2}:[0-9]{2}')
then concat(substring(date_field,1,10),' ',substring(date_field,12,8))
-- yyyy/MM/dd
when REGEXP_LIKE(date_field, '^[0-9]{4}/[0-9]{2}/[0-9]{2}$') then TO_CHAR(TO_DATE(date_field, 'yyyy/MM/dd'), 'yyyy-MM-dd')
-- yyyyMMdd
when REGEXP_LIKE(date_field, '^[0-9]{4}[0-9]{2}[0-9]{2}$') then TO_CHAR(TO_DATE(date_field, 'yyyyMMdd'), 'yyyy-MM-dd')
else '数据格式不规范' end ywsj_update