【오라클(Oracle) SQL 함수】 VALIDATE_CONVERSION 함수

VALIDATE_CONVERSION

 

---

문 법

 

validate_conversion::=

 

---

목 적

VALIDATE_CONVERSION 함수는 expr 인수를 지정된 데이터 형식으로 변환할 수 있는지 여부를 반환한다.

expr 인수가 성공적으로 변환될 수 있으면 1을 반환하고, 그렇지 않으면 이 함수는 0을 반환한다. expr 인수가 null로 평가되면 이 함수는 1을 반환한다. expr 인수를 평가하는 동안 오류가 발생하면 오류를 반환한다.

expr 인수에는 SQL 표현식을 지정한다. expr 인수에 사용할 수 있는 데이터 유형과 선택적 옵션 fmt 인수 및 nlsparam 인수의 용도는 type_name 인수에서 지정하는 데이터 유형에 따라 달라진다.

type_name 인수에는 expr 인수를 변환하기 위한 데이터 타입을 지정한다. 다음과 같은 데이터 유형을 지정할 수 있다.

 

BINARY_DOUBLE

BINARY_DOUBLE 을 지정한 경우 expr 인수에는 CHAR, VARCHAR2, NCHAR 또는 NVARCHAR2 데이터 타입의 문자열이나 NUMBER, BINARY_FLOAT 또는 BINARY_DOUBLE 타입의 수치형 값으로 평가되는 모든 표현식이 사용 가능하다. 선택적 옵션 fmt 와 nlsparam 인수는 TO_BINARY_DOUBLE 함수와 동일한 용도로 사용된다. 자세한 내용은 TO_BINARY_DOUBLE을 참조.

 

BINARY_FLOAT

BINARY_FLOAT 를 지정한 경우 expr 인수에는 CHAR, VARCHAR2, NCHAR 또는 NVARCHAR2 데이터 타입의 문자열이나 NUMBER, BINARY_FLOAT 또는 BINARY_DOUBLE 타입의 수치형 값으로 평가되는 모든 표현식이 사용 가능하다. 선택적 옵션 fmt 및 nlsparam 인수는 TO_BINARY_FLOAT 함수와 동일한 용도로 사용된다. 자세한 내용은 TO_BINARY_FLOAT를 참조.

 

DATE

DATE 를 지정한 경우 expr 인수에는 CHAR, VARCHAR2, NCHAR 또는 NVARCHAR2 데이터 타입의 문자열로 평가되는 모든 표현식이 사용 가능하다. 선택적 옵션 fmt 및 nlsparam 인수는 TO_DATE 함수와 동일한 용도로 사용된다. 자세한 내용은 TO_DATE를 참조.

 

INTERVAL DAY TO SECOND

INTERVAL DAY TO SECOND 를 지정한 경우 expr 인수에는 CHAR, VARCHAR2, NCHAR 또는 NVARCHAR2 데이터 타입의 문자열로 평가되는 모든 표현식이 사용 가능하며, SQL 구간 간격(기간) 형식 또는 ISO 기간 형식의 값을 포함해야 한다. 선택적 옵션 fmt 및 nlsparam 인수는 이 데이터 유형에 적용되지 않는다. SQL 기간 간격 형식과 ISO 기간(ISO duration) 형식에 대한 자세한 내용은 TO_DSINTERVAL을 참조하세요.

 

INTERVAL YEAR TO MONTH

INTERVAL YEAR TO MONTH를 지정한 경우 expr 인수에는 CHAR, VARCHAR2, NCHAR 또는 NVARCHAR2 데이터 타입의 문자열로 평가되는 모든 표현식이 사용 가능하며, SQL 구간 간격 형식 또는 ISO 기간(ISO duration) 형식의 값을 포함해야 한다. 선택적 옵션 fmt 및 nlsparam 인수는 이 데이터 유형에 적용되지 않는다.

 

NUMBER

NUMBER를 지정한 경우 expr 인수에는 CHAR, VARCHAR2, NCHAR 또는 NVARCHAR2 데이터 타입의 문자열이나 NUMBER, BINARY_FLOAT 또는 BINARY_DOUBLE 타입의 수치형 값으로 평가되는 모든 표현식이 사용 가능하다. 선택적 옵션 fmt 및 nlsparam 인수는 TO_NUMBER 함수와 동일한 용도로 사용된다.

 

TIMESTAMP

TIMESTAMP를 지정한 경우 expr 인수는 CHAR, VARCHAR2, NCHAR 또는 NVARCHAR2 데이터 타입의 문자열로 평가되는 모든 표현식이 사용 가능하다. 선택적 옵션 fmt 및 nlsparam 인수는 TO_TIMESTAMP 함수와 동일한 용도로 사용된다. fmt 인수를 생략하면 expr 인수는 TIMESTAMP 데이터 유형의 기본 형식이어야 하며, 이 형식은 NLS_TIMESTAMP_FORMAT 초기화 파라미터(initialization parameter)에 의해 결정된다. 자세한 내용은 TO_TIMESTAMP를 참조

 

TIMESTAMP WITH TIME ZONE

TIMESTAMP WITH TIME ZONE 을 지정한 경우 expr 인수에는 CHAR, VARCHAR2, NCHAR 또는 NVARCHAR2 데이터 타입의 문자열로 평가되는 모든 표현식이 사용 가능하다. 선택적 옵션 fmt 및 nlsparam 인수는 TO_TIMESTAMP_TZ 함수와 동일한 용도로 사용된다.

fmt 인수를 생략하면 expr 인수는 TIMESTAMP WITH TIME ZONE 데이터 타입의 기본 형식이어야 하며, 이 형식은 NLS_TIMESTAMP_TZ_FORMAT 초기화 파라미터(initialization parameter)에 의해 결정된다. 자세한 내용은 TO_TIMESTAMP_TZ를 참조

 

TIMESTAMP WITH LOCAL TIME ZONE

TIMESTAMP를 지정한 경우 expr 인수에는 CHAR, VARCHAR2, NCHAR 또는 NVARCHAR2 데이터 타입의 문자열로 평가되는 모든 표현식이 사용 가능하다. 선택적 옵션 fmt 및 nlsparam 인수는 TO_TIMESTAMP 함수와 동일한 용도로 사용된다. fmt 인수를 생략하면 expr 인수는 TIMESTAMP 데이터 타입의 기본 형식이어야 하며, 이 형식은 NLS_TIMESTAMP_FORMAT 초기화 파라미터에 의해 결정된다. 자세한 내용은 TO_TIMESTAMP를 참조

 

---

관련 함수

함수명	함수내용
CAST	데이터 형식이나 collection 형식을 다른 데이터 형식이나 collection 형식으로 변환한다.
TO_CHAR (number)	수치형의 값 n을 옵션의 number 포맷 fmt에서 지정한 VARCHAR2 데이터 타입의 문자열 값으로 변환한다. 수치형 데이터를 문자열로 변환하는 함수이다.
TO_CHAR (datetime)	datetime 또는 날짜의 구간 값, TIMESTAMP, TIME 구역을 가지는 TIMESTAMP, 또는 TIMESTAMP WITH LOCAL TIME ZONE 데이터 형을 date 포맷 fmt에서 지정한 포맷의 VARCHAR2 데이터 타입의 문자열 값으로 변환(문자형 날짜 함수)한다. 날짜형 데이터를 문자열로 변환하는 함수이다.
TO_CHAR (character)	NCHAR, NVARCHAR2, CLOB, NCLOB 데이터를 데이터 베이스 문자 세트로 변환한다. (문자변환함수)
TO_DATE	CHAR, VARCHAR2, NCHAR, NVARCHAR2 데이터형의 문자열 char을 날짜형 데이터 타입 값으로 변환하는 형 변환 작업을 수행한다. 문자형 날짜 데이터를 날짜형 데이터로 변환하는 함수이다.
TO_NUMBER	인수 expr을 NUMBER 데이터형의 값으로 변환한다.
VALIDATE_CONVERSION	expr 인수가 지정된 데이터 형식으로 변환될 수 있는지 여부를 반환한다.

[오라클 SQL 함수(Oracle SQL Function) 목록 리스트 링크]

 

---

예 제

다음 개별 구문에서 지정된 값을 지정한 데이터 유형으로 정상적으로 변환할 수 있다. 따라서 개별 구문은 1의 값을 반환한다.

Oracle Program

SELECT VALIDATE_CONVERSION(1000 AS BINARY_DOUBLE)
  FROM DUAL;

SELECT VALIDATE_CONVERSION('1234.56' AS BINARY_FLOAT)
  FROM DUAL;

SELECT VALIDATE_CONVERSION('July 20, 1969, 20:18' AS DATE,
    'Month dd, YYYY, HH24:MI', 'NLS_DATE_LANGUAGE = American')
  FROM DUAL;

SELECT VALIDATE_CONVERSION('200 00:00:00' AS INTERVAL DAY TO SECOND)
  FROM DUAL;

SELECT VALIDATE_CONVERSION('P1Y2M' AS INTERVAL YEAR TO MONTH)
  FROM DUAL;

SELECT VALIDATE_CONVERSION('$100,00' AS NUMBER,
    '$999D99', 'NLS_NUMERIC_CHARACTERS = '',.''')
  FROM DUAL;

SELECT VALIDATE_CONVERSION('29-Jan-02 17:24:00' AS TIMESTAMP,
    'DD-MON-YY HH24:MI:SS')
  FROM DUAL;

SELECT VALIDATE_CONVERSION('1999-12-01 11:00:00 -8:00'
    AS TIMESTAMP WITH TIME ZONE, 'YYYY-MM-DD HH:MI:SS TZH:TZM')
  FROM DUAL;

SELECT VALIDATE_CONVERSION('11-May-16 17:30:00'
    AS TIMESTAMP WITH LOCAL TIME ZONE, 'DD-MON-YY HH24:MI:SS')
  FROM DUAL;

 

---

다음 예제는 지정한 값을 BINIAR_FLOAT로 변환할 수 없으므로 0을 반환한다.

Oracle Program

SELECT VALIDATE_CONVERSION('$29.99' AS BINARY_FLOAT)
FROM   DUAL;

 

---

다음 예제는 지정된 수치형 출력 형식 모델을 사용하여서 해당 값을 BINARY_FLOAT 타입으로 변환할 수 있으므로 1을 반환한다.

Oracle Program

SELECT VALIDATE_CONVERSION('$29.99' AS BINARY_FLOAT, '$99D99')
FROM   DUAL;

 

---

날짜 유효성 체크 - VALIDATE_CONVERSION 함수

주민번호를 통한 연령 계산 시 주민번호 오류 등으로 인하여 날짜 타입 변환하는 경우 에러가 발생하는 경우가 종종 발생.

VALIDATE_CONVERSION 함수를 통하여 날짜 유효성 체크를 통하여 오류건에 대하여 분기 처리가 가능함.

이를 통하여 연령 계산 시 사용 가능 할 듯 합니다. 다만 로직 체크에 따라 속도는 문제가 될 수 있을 듯 합니다.

Oracle Program

SELECT VALIDATE_CONVERSION( CASE WHEN SUBSTR(A.주민번호,7,1) IN ('1','2','5','6') THEN '19'||SUBSTR(A.주민번호,1,6)
                                 WHEN SUBSTR(A.주민번호,7,1) IN ('3','4','7','8') THEN '20'||SUBSTR(A.주민번호,1,6)
                                 WHEN SUBSTR(A.주민번호,7,1) IN ('9','0')         THEN '18'||SUBSTR(A.주민번호,1,6)
                            END AS DATE, 'YYYYMMDD') 생년월일유효성,
                            
       CASE WHEN SUBSTR(A.주민번호,7,1) IN ('1','2','5','6') THEN '19'||SUBSTR(A.주민번호,1,6)
            WHEN SUBSTR(A.주민번호,7,1) IN ('3','4','7','8') THEN '20'||SUBSTR(A.주민번호,1,6)
            WHEN SUBSTR(A.주민번호,7,1) IN ('9','0')         THEN '18'||SUBSTR(A.주민번호,1,6)
       END 생년월일,     
       A.주민번호,
       A.*
FROM   BACK A
WHERE  1=1
  AND  INPUT_DT >='20200101'

 

---

 

  --------------------------------------------  

오라클 SQL 함수(Oracle SQL Function) 목록 리스트 링크

 

[SQL, Pandas, R Prog, Dplyr, SQLDF, PANDASQL, DATA.TABLE] SQL EMP 예제로 만나는 테이블 데이터 처리 방법 리스트 링크

2024.01 수정