[Oracle] 일자체크(REGEXP_LIKE)

 

WITH TEMP_A AS
(
SELECT '20130325' AS C_DATE FROM DUAL
UNION ALL
SELECT NULL       AS C_DATE FROM DUAL
UNION ALL
SELECT ' '        AS C_DATE FROM DUAL
UNION ALL
SELECT '2013 129' AS C_DATE FROM DUAL
UNION ALL
SELECT '20130231' AS C_DATE FROM DUAL
UNION ALL
SELECT '20130229' AS C_DATE FROM DUAL
UNION ALL
SELECT '20130228' AS C_DATE FROM DUAL
)
SELECT C_DATE
     , CASE WHEN C_DATE IS NULL THEN 'NULL'
            WHEN -- 숫자형태로서 YYYYMMDD 8자리이어야 하고, 01,03,05,07,08,10,12월은 31일까지가 최대이고, 02월은 최대가 29일이며, 04,06,09,11월은 30일이 최대임.
                 NOT REGEXP_LIKE(C_DATE, '^[0-9]{4}(((0[13578]|(10|12))(0[1-9]|[1-2][0-9]|3[0-1]))|(02(0[1-9]|[1-2][0-9]))|((0[469]|11)(0[1-9]|[1-2][0-9]|30)))$')
            THEN '날짜형식이 틀림'
            WHEN -- 년도가 400으로 나누어지거나, 4로 나누어지고 100으로 나누어지지 않는 윤년이 아닌 경우
                 NOT REGEXP_LIKE(SUBSTR(C_DATE, 1, 4), '((1[6-9]|[2-9]\d)([02468][48]|[2468][048]|[13579][26]))|((16|[2468][048]|[3579][26])00)')
                 AND SUBSTR(C_DATE, 5, 2) = '02' AND SUBSTR(C_DATE, 7, 2) = '29'
            THEN '평년인데 2월달이 29일임'
            ELSE '정상'
       END AS DATE_CHECK
  FROM TEMP_A

 

결과)

20130325 정상
 NULL  날짜형식이 틀림
2013 129 날짜형식이 틀림
20130231 날짜형식이 틀림
20130229 평년인데 2월달이 29일임
20130228 정상