法律诉讼中，判决日期中出现汉字日期、全角日期、错误字符等数据。光是0的形式就有很多种：Ο○o〇零。

  • 二〇二〇年十一月二十四日
  • 二0一七年九月二十二日
  • 二 ○ 二 ○ 年 六 月 三 日
  • 二〇一九年五月二十二日 执行员　　陈　良

早期版本的写法如下，300万数据，执行时长约为12个多小时后，而且卡着不动，最终kill了这个任务：

汉字年月日转化为阿拉伯数字的年月日

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
select 
        cid,
        case_reason,
        full2half(replace(SUBSTR(COALESCE(trim(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(
    CONCAT(split(judge_time,'年')[0],
         if(length(regexp_replace(regexp_replace(split(split(judge_time,'年')[1],'月')[0],'^十$','一〇'),'^十','一'))=1,
      concat('〇',regexp_replace(regexp_replace(split(split(judge_time,'年')[1],'月')[0],'^十$','一〇'),'^十','一')),
      regexp_replace(regexp_replace(split(split(judge_time,'年')[1],'月')[0],'^十$','一〇'),'^十','一')),
     if(length(regexp_replace(regexp_replace(regexp_replace(regexp_replace(regexp_replace(replace(split(split(judge_time,'年')[1],'月')[1],'日',''),
      '^十$','一〇'),'十$','〇'),'^十','一'),'^二十','二'),'^三十','三'))=1,
      concat('〇',regexp_replace(regexp_replace(regexp_replace(regexp_replace(regexp_replace(replace(split(split(judge_time,'年')[1],'月')[1],'日',''),
      '^十$','一〇'),'十$','〇'),'^十','一'),'^二十','二'),'^三十','三')),
      regexp_replace(regexp_replace(regexp_replace(regexp_replace(regexp_replace(replace(split(split(judge_time,'年')[1],'月')[1],'日',''),
      '^十$','一〇'),'十$','〇'),'^十','一'),'^二十','二'),'^三十','三')))
          ,'〇','0'),'一','1'),'二','2'),'三','3'),'四','4'),'五','5'),'六','6'),'七','7'),'八','8'),'九','9')),''),0,4),'○','0')) judge_time
       -- count(1) cnt
from dwd_creditrisk_xy_company_sffx.company_sffx_flss_desc

通过translate函数对上述脚本优化，所用函数从50多个减少为8个，同样的数据执行时间仅需要19秒多（状态为带着失败成功，原因是substr函数中log4j的版本太低，日志报错：java.lang.NoSuchFieldError: EMPTY_BYTE_ARRAY），下面的代码中，特殊点在于处理“十”这个字符，有以下,四种情况：

  • 十在两个数字中间，可以删除，比如二十三，转换为 23
  • 十是单独一个数字，表示10，转为 10
  • 十是首位，后面跟其他数字，转为1，如十五，转换为 15,
  • 十是末位，转换为0，比如二十，转为 20

汉字年月日转化为阿拉伯数字的年月日

1
2
3
4
5
6
7
8
9
10
11
12
13
14
select 
    cid as cc,
    case_reason,
    substr(trim(regexp_replace(
        regexp_replace(
            regexp_replace(
                regexp_replace(
                    translate(nvl(judge_time, ''), 'Ο○o〇零一二三四五六七八九年月日', '00000123456789--'),
                '(?<=[0-9])十(?=[0-9])', ''),
            '(?<![0-9])十(?![0-9])', '10'),
        '(?<![0-9])十(?=[0-9])', '1'),
    '(?<=[0-9])十(?![0-9])', '0')),1,4)
    as judge_time
from dwd_creditrisk_xy_company_sffx.company_sffx_flss_desc

转换后的取出年月的效果如下：

cc case_reason judge_time
195749267 劳动争议 2016
742557060 其他 2015
739895389 其他 2016
2352829149 其他 2017
3117298023 其他 2017
191030462 买卖合同纠纷 2016
173523182 其他 2017
209603208 其他 2017
3134427034 其他 2017
543988346 其他 2017

如果仅需要年份，上面语句还能优化，把对月份和日期的替换逻辑删掉：

1
2
3
4
5
6
select 
    cid as cc,
    case_reason,
    substr(trim(translate(nvl(judge_time, ''), 'Ο○o〇零一二三四五六七八九', '00000123456789')),1,4)
as judge_time
from dwd_creditrisk_xy_company_sffx.company_sffx_flss_desc