[ORACLE] 오라클 문자열의 날짜 형식을 확인하는 방법

오라클 문자열의 날짜 형식을 확인하는 방법

 

오라클에서는 쿼리문에서 문자열의 날짜를 TO_DATE 구문으로 변환할때 형식이 지정된 포맷에 맞지 않으면 "ORA-01839: 지정된 월에 대한 날짜가 부적합합니다." 라는 오류가 발생하게 됩니다. 쿼리문에 지정된 날짜 포맷에 대하여 문자열이 올바르지 않은 형식으로 들어오는 경우입니다. 이럴때 인자값으로 조회된 문자열이 TO_DATE에 지정된 형식에 부합하는지를 체크하여 오류를 해결할 수 있습니다. 체크하는 방법으로는 크게 3 가지가 있습니다.

 

1. 사용자 정의함수를 생성하여 확인

2. REGEXP_INSTR 정규식을 확인하는 오라클 내장함수를 이용하는 방법 ( 오라클 10g 이상 )

3. VALIDATE_CONVERSION 함수를 사용하는 방법 ( 오라클 12c R2 이상 )

 

 

사용자 정의함수를 이용한 문자열 형식 확인

오라클에 IS_MATCHED_TYPE_DATE라는 사용자 정의 함수를 아래와 같이 생성합니다.

CREATE OR REPLACE FUNCTION IS_MATCHED_TYPE_DATE(p_string VARCHAR2, p_format VARCHAR2)
    RETURN NUMBER
IS
    v_date DATE;
BEGIN
    v_date := TO_DATE(p_string, p_format);
    RETURN 1;
EXCEPTION
    WHEN OTHERS THEN 
        RETURN 0;
END;
/

 

함수에서는 문자열과 타입을 함수의 인자값으로 입력을 받아서 실제 TO_DATE 함수를 실행하여 EXCEPTION을 발생하여 올바른 유형인지 확인합니다. 문자열이 인자값으로 받은 타입과 같은 유형이라면 1을, 아니라면 0을 리턴하게 됩니다.

 

위 함수를 올바른 타입과 올바르지 않은 타입의 문자열로 실행해봅니다.

 

SELECT IS_MATCHED_TYPE_DATE('2021-11-11', 'YYYY-MM-DD') AS RESULT_TRUE,
       IS_MATCHED_TYPE_DATE('2021-11-100', 'YYYY-MM-DD') AS RESULT_FALSE 
  FROM DUAL;

RESULT_TRUE가 올바른 문자열이고 RESULT_FALSE가 날짜가 100인 올바르지 않은 문자열입니다. 실행 결과는 아래와 같습니다.

 

쿼리 수행 결과

 

REGEXP_INSTR 정규식을 확인하는 오라클 내장함수를 이용하는 방법

오라클 10G 부터 도입된 REGEXP_INSTR 함수는 정규식을 활용하여 인자값이 지정된 정규식을 만족하는지 여부를 확인하여 매치하는지 여부를 리턴합니다.

 

SELECT REGEXP_INSTR('2021-11-11', '^((19|20)\d{2})-(0[1-9]|1[012])-(0[1-9]|[12][0-9]|3[01])') AS RESULT_TRUE,
       REGEXP_INSTR('2021-11-50', '^((19|20)\d{2})-(0[1-9]|1[012])-(0[1-9]|[12][0-9]|3[01])') AS RESULT_FALSE
  FROM DUAL

 

위 패턴으로 실행하면 정해진 년, 월, 일 길이의 패턴을 판별하여 정해진 날짜 형식인지 아닌지 여부를 결과로 1(TRUE)과 0(FALSE)로 리턴합니다. 하지만 윤달이나 정해진 길이의 년, 월, 일이 아닌 경우(예를 들어, 날짜가 111일인 경우 등) 에는 패턴을 체크하지 못해 1로 리턴하는 등의 제약이 있습니다. 실제 정규식으로 실제하는 날짜인지 여부를 확인하려면 보다 정규하고 장황한 정규식이 필요합니다. 

 

 

VALIDATE_CONVERSION 함수를 사용하는 방법

문자열이 정확한 날짜 형식인지를 확인하는 또 다른 방법으로는 VALIDATION_CONVERSION 함수를 사용하는 것입니다. 이 함수는 오라클 12 부터 도입된 함수(오라클 설명 자료)입니다. 이 함수는 날짜 뿐만 아니라 다양한 숫자, 문자 형식에 대응하여 해당 문자열이 입력된 형식에 부합하는 지를 확인해줍니다. 문자열이 정해진 형식의 날짜인지를 확인하려면 오라클 12버전 이상에서 아래와 같이 쿼리를 실행해보면 됩니다.

 

SELECT VALIDATE_CONVERSION('2021-11-11' AS DATE, 'YYYY-MM-DD') FROM DUAL

 

위 쿼리를 실행해서 문자열이 정해진 형식의 날짜라면 1, 아니면 0을 리턴해줍니다.

 

 

마무리

오라클에서 특정문자열이 정해진 날짜 형식에 맞는지 체크하는 방법에 대해서 다뤄봤습니다. 오라클에서 지원하는 REGEXP_INSTR, VALIDATE_CONVERSION이라는 좋은 내장함수들이 있지만 아무래도 버전에 관계없이 데이터 형식을 비교하기에는 사용자정의함수를 사용하는 것이 더 깔끔해보입니다.  하지만 사용하는 오라클의 버전과 체크하는 형식에 따라서 적절히 내장함수를 활용하는 것도 나쁘지 않은 선택일 것 같습니다. 아무래도 오라클 내장함수기 때문에 지원하는 타입도 다양하니까 불필요하게 사용자정의함수를 늘리지 않고 사용할 수 있으니까요.