oracle字符串清洗、拆分案例

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

源数据示例：

结果示例：