Oracle 非標準日期字符串清洗遷移方案：PL/SQL 正則 + Java 預處理雙管齊下

問題痛點

VARCHAR2 列存儲混亂日期格式：'2013.2.1 10:32:32'、'20130201 103232'、'2013/0201 10 32 32'，甚至非法值 '2013/2/1 240000'、'2013/2/30 10:32:32'。純 SQL TO_DATE 無法通用解析。

推薦策略：曲線救國 — 程序預處理

1. 導出數據：SELECT id, raw_dt FROM src_table 讀入 Java/Python 腳本。
2. 多模式解析：定義格式列表 ["yyyy.M.d HH:mm:ss", "yyyyMMdd HHmmss", "yyyy/MMdd HH mm ss", ...]，嘗試 DateTimeFormatter 循環解析，捕獲異常。
3. 語義校驗：解析成功後檢查月份 1-12、日期合法性、時分秒範圍，24:00:00 折算為次日 00:00:00。
4. 標準化輸出：轉為 TIMESTAMP 字面量或 DATE 類型，批量 INSERT 目標表。
5. 異常記錄：無法解析/校驗失敗的行寫入錯誤表人工核對。

輔助 SQL 篩查

-- 快速定位疑似非法行
SELECT * FROM src_table
WHERE NOT REGEXP_LIKE(raw_dt, '^\d{4}[-./]\d{1,2}[-./]\d{1,2}');

工具推薦

• Java: java.time.format.DateTimeFormatterBuilder 寬容解析。
• Python: dateutil.parser.parse 自動識別多種格式。
• ETL 工具: Kettle (PDI)、DataX 內置日期清洗組件。