oracle字符串清洗、拆分案例

需求描述

需求： 对给定的字符，按照点号对其进行拆分并以表的形式返回结果。

解决方法：通过 translate，结合自定义自增表、SUBSTR来实现字符串拆分。

SQL 代码

SELECT IDNO,MON,TO_DATE(MON,'YYYYMM') New_Day,SYSDATE-365*3 FROM
(
        SELECT i_year,IDNO,SUBSTR(ID,1,b.lv),b.lv,INSTR(ID,',',1,b.lv)-1,

        CASE WHEN b.lv = 1 THEN i_year||SUBSTR(ID,1,INSTR(ID,',',1,b.lv)-1)
        WHEN b.lv = t.LEN THEN i_year||SUBSTR(ID,INSTR(ID,',',1,b.lv-1)+1,LENGTH(ID)) 
        WHEN b.lv > 1 AND b.lv<t.LEN THEN i_year||SUBSTR(ID,INSTR(ID,',',1,b.lv-1)+1,INSTR(ID,',',1,b.lv)-INSTR(ID,',',1,b.lv-1)-1 ) END mon
        FROM   
              (
                SELECT i_year,
                i_new_date,
                TRIM(','  from i_new_date) ID,
                LENGTH(TRIM(','  from i_new_date))-LENGTH(REPLACE(TRIM(','  from i_new_date),',',''))+1 LEN,
                 IDNO FROM
                    (
                    SELECT A.*,
                    SUBSTR(longvarchar,1,4) i_year,SUBSTR(longvarchar,5,LENGTH(longvarchar)) i_date,
                   REPLACE(translate(SUBSTR(TRIM(longvarchar),5,LENGTH(longvarchar)),'.，、～- ','######'),'#',',')i_new_date
                    FROM test_table A
                    )B
                    WHERE IDNO = '12345678'
              
              )t,(select level lv from dual connect by level < 13) b
              where b.lv <=t.LEN 
    
    )A
    WHERE TO_DATE(MON,'YYYYMM') > SYSDATE-365*3

数据示例

源数据示例：

IDNO	longvarchar
12345678	2016.1.2.4.
1234567	.2015.1.2.4.
123456	.2014.1.2.4

结果示例：

IDNO	I_year	longvarchar
12345678	2016	1
12345678	2016	2
12345678	2016	4