-- 데이터가 '19920131' 와 같이 8자리 숫자로 입력되어야 한다는 전체하에 WITH T AS ( SELECT '1992.01.31(30일간)' STR FROM DUAL UNION ALL SELECT '1992.01.31' FROM DUAL UNION ALL SELECT '1992013130' FROM DUAL UNION ALL SELECT '19920131' FROM DUAL ) SELECT T.STR, CASE WHEN LENGTH(T.STR) > 8 THEN '사용기간이아닙니다' WHEN REGEXP_REPLACE(T.STR, '[0-9]') IS NULL THEN T.STR END RESULT FROM T;
WITH t AS ( SELECT '1992.01.31(30일간)' a FROM dual UNION ALL SELECT '1234' FROM dual UNION ALL SELECT '19920131' FROM dual UNION ALL SELECT '19920229' FROM dual UNION ALL SELECT '1992.02.29' FROM dual UNION ALL SELECT '19930229' FROM dual UNION ALL SELECT '12가나다34' FROM dual ) SELECT a , CASE WHEN REGEXP_LIKE(a, '[^0-9]') OR TO_NUMBER(column_value) = 0 THEN '사용기간이아닙니다' ELSE a END x FROM t , XMLTABLE('if (. castable as xs:date) then 1 else 0' PASSING a) ;
WITH T AS ( SELECT '1992.01.31(30일간)' STR FROM DUAL UNION ALL SELECT '1992.01.31' FROM DUAL UNION ALL SELECT '1992013130' FROM DUAL UNION ALL SELECT '19920136' FROM DUAL UNION ALL SELECT '07-01-1992' FROM DUAL UNION ALL SELECT '1993-05-25' FROM DUAL UNION ALL SELECT '19930525' FROM DUAL UNION ALL SELECT '20170229' FROM DUAL ) SELECT STR, CASE WHEN STR = (CASE WHEN REGEXP_SUBSTR(STR,'^[[:digit:]]{8}$') IS NOT NULL THEN TO_CHAR(ADD_MONTHS(TO_DATE('19000101','YYYYMMDD'), (TO_NUMBER(SUBSTR(STR,1,4))-1900)*12 + TO_NUMBER(SUBSTR(STR,5,2))-1) +TO_NUMBER(SUBSTR(STR,7,2))-1, 'YYYYMMDD') ELSE NULL END) THEN STR ELSE '사용기간이아닙니다' END FROM T;
/* 억지로 만들자고 하면*/ WITH T AS ( SELECT '1992.01.31(30일간)' TXT FROM DUAL UNION ALL SELECT '20162029' TXT FROM DUAL UNION ALL SELECT '20160229' TXT FROM DUAL ) SELECT TXT , CASE WHEN REGEXP_COUNT(TXT,'[^0-9]') >= 1 THEN '사용기간이아닙니다' WHEN REGEXP_COUNT(TXT,'[0-9]{4}[0-1][0-9][0-3][0-9]') = 0 THEN '사용기간이아닙니다' WHEN SUBSTR(TXT,7,2) <= TO_CHAR(LAST_DAY(TO_DATE(SUBSTR(TXT,1,6),'YYYYMM')),'DD') THEN TXT END TXT FROM T
20160239, 20161330 ㅋㅋ
WITH T AS ( SELECT '1992.01.31(30일간)' TXT FROM DUAL UNION ALL SELECT '20162029' TXT FROM DUAL UNION ALL SELECT '20162039' TXT FROM DUAL UNION ALL SELECT '20161330' TXT FROM DUAL UNION ALL SELECT '20160229' TXT FROM DUAL ) SELECT TXT , CASE WHEN REGEXP_COUNT(TXT,'^[0-9]{4}((0[1-9])|(1[1-2]))(([0-2][0-9])|(3[0-1]))$') = 0 THEN '사용기간이아닙니다' WHEN SUBSTR(TXT,7,2) <= TO_CHAR(LAST_DAY(TO_DATE(SUBSTR(TXT,1,6),'YYYYMM')),'DD') THEN TXT ELSE '사용기간이아닙니다' END TXT FROM T