[Oracle] 함수를 이용한 숫자, 날짜 데이터 변환 / 형변환 오류 처리

EUN JY·2022년 8월 11일

Database

목록 보기

8/21

1. 데이터 변환

1-1. 문자열 숫자 변환

TO_NUMBER() : 문자열을 정수, 실수로 변환
CAST() : 자릿수를 정의하여 정수, 실수로 변환

SELECT TO_NUMBER('123') FROM DUAL; /* 123 */
SELECT CAST('123' AS NUMBER(3)) FROM DUAL; /* 123 */
SELECT TO_NUMBER('123.5') FROM DUAL; /* 123.5 */
SELECT CAST('123.55' AS NUMBER(3)) FROM DUAL; /* 124 */
SELECT CAST('123.55' AS NUMBER(5, 2)) FROM DUAL; /* 123.55 */

1-1-1. 문자열 숫자 변환 오류 처리

문자열에 변환 불가한 문자가 포함되어 있으면 오류가 발생함

SELECT TO_NUMBER('13,000,000') FROM DUAL;

REPLACE() 함수 또는 REGEXP_REPLACE() 정규식 함수를 이용하여 해당 문자열 제거
REGEXP_REPLACE() 는 Oracle 10g 이상부터 사용 가능

-- 직접 해당 문자열을 제거 (마지막 인자 생략 가능)
SELECT REPLACE(REPLACE('* 13,000,000', ','), '*') FROM DUAL;
-- 정규식으로 숫자(소수점, 음수 포함) 외 문자 모두 제거
SELECT REGEXP_REPLACE('* -13,000,000', '[^0-9.-]', '') FROM DUAL;

1-1-2. 문자열 숫자 변환 가능 여부 체크

TRANSLATE('문자열', '대상문자', '변환문자') : 문자열 치환 함수(Oracle 8i 이상 사용 가능)
- 대상문자와 변환문자가 1:1로 변환되며, 대상문자에는 있지만 변환문자에 없는 문자는 제거됨
- 아래의 경우 대상문자 'A'는 그대로 'A'로 변환되지만, 숫자와 부호들은 변환문자에 없기 때문에 제거됨

SELECT 
    CASE WHEN TRANSLATE('-123.456', 'A1234567890.+-', 'A') IS NULL 
    THEN TO_NUMBER('-123.456') END
FROM DUAL;

REGEXP_INSTR('문자열', '패턴') : 특정 패턴이 출현하는 첫 위치 값을 반환(Oracle 10g 이상 사용 가능)

SELECT 
    CASE WHEN REGEXP_INSTR('-123.456','^[+-]?\d*(\.?\d*)$') = 1 /* 숫자일 경우 1 반환 */
    THEN TO_NUMBER('-123.456') END
FROM DUAL;

VALIDATE_CONVERSION() : Oracle 12c 부터 생긴 형변환 체크 함수

SELECT 
    CASE WHEN VALIDATE_CONVERSION('-123.456' AS NUMBER) = 1
    THEN TO_NUMBER('-123.456') END
FROM DUAL;

1-1-3. 문자열 숫자 변환 오류 대처

형변환 오류 발생 시 지정한 대체 값을 출력하도록 함

SELECT 
    TO_NUMBER('12345' DEFAULT -1 ON CONVERSION ERROR) AS res1 /* 12345 */
    , TO_NUMBER('12345A' DEFAULT -1 ON CONVERSION ERROR) AS res2 /* -1 */
    , CAST('12345' AS NUMBER(10) DEFAULT -1 ON CONVERSION ERROR) AS res3 /* 12345 */
    , CAST('12345A' AS NUMBER(10) DEFAULT -1 ON CONVERSION ERROR) AS res4 /* -1 */
FROM DUAL;

1-1-4. 문자열로 숫자 조회

TO_CHAR([컬럼명], [포맷]) 함수 이용

SELECT TO_CHAR('123', 'FM00000') FROM DUAL; /* 00123 */
SELECT TO_CHAR('100000', 'FM999,999,999,999') FROM DUAL; /* 100,000 */
SELECT TO_CHAR('100000', 'FML999,999,999') FROM DUAL; /* ￦100,000 */
SELECT TO_CHAR('3.1415926', 'FM990.99') FROM DUAL; /* 3.14 */

1-2. 문자열 날짜 변환

현재 일시 출력 : SELECT SYSDATE FROM DUAL;
TO_DATE([문자열], [날짜 포맷]) 를 이용하여 문자열을 날짜 데이터로 변환 가능
- 날짜 포맷은 대소문자 구분 없음
- 12시간 단위는 HH, 24시간 단위는 HH24로 표기

SELECT 
    TO_DATE('2022-08-12 13:50:00', 'YYYY-MM-DD HH24:MI:SS')
    , TO_DATE('11.08.2022', 'DD.MM.YYYY')
    , TO_DATE('220810', 'YYMMDD')
FROM DUAL;

시스템 설정 날짜 형식과 동일하면 날짜 포맷을 입력하지 않아도 됨

SELECT * FROM nls_session_parameters WHERE parameter = 'NLS_DATE_FORMAT'; /* 결과 : RR/MM/DD */
SELECT TO_DATE('2022/08/12') FROM DUAL; /* 정상 출력 */
SELECT TO_DATE('12/08/2022') FROM DUAL; /* 오류 발생 */

1-2-1. 문자열 날짜 변환 오류 처리

REGEXP_INSTR() 정규식 함수 사용(Oracle 10g 이상)
- 날짜 형식 체크는 가능하지만, 유효한 값인지 판단하지 못함
VALIDATE_CONVERSION() 형식 체크 함수 사용(Oracle 12c R2 이상)
- 날짜 형식이 맞으면 '1', 틀리면 '0' 반환

SELECT REGEXP_INSTR('2022-08-08', '^\d{4}-\d{2}-\d{2}') FROM DUAL;
SELECT VALIDATE_CONVERSION('2022-08-12' AS DATE, 'YYYY-MM-DD') FROM DUAL;

IS_DATE() 사용자 정의 함수 사용

-- DATE 형식이 맟을 경우 '1'을 반환하도록 함
CREATE FUNCTION IS_DATE(dStr VARCHAR2, dFormat VARCHAR2)
    RETURN NUMBER
IS
    resDate DATE;
BEGIN
    resDate := TO_DATE(dStr, dFormat);
    RETURN 1;
EXCEPTION
    WHEN OTHERS THEN
        RETURN 0;
END;
/

-- 테스트
SELECT IS_DATE('2022-08-12', 'YYYY-MM-DD') FROM DUAL;
SELECT IS_DATE('2022-08-12', 'YYYYMMDD') FROM DUAL;

-- 함수 조회
SELECT * FROM USER_OBJECTS WHERE OBJECT_TYPE = 'FUNCTION';
DROP FUNCTION IS_DATE_TEST7;

1-2-2. 문자열로 날짜 조회

다음과 같이 저장한 데이터를 출력

-- 데이터 INSERT
INSERT INTO TAB3 VALUES (TO_DATE('2022-08-12 13:50:00', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO TAB3 VALUES (TO_DATE('2022-08-11', 'YYYY-MM-DD'));

날짜와 시간 조회
- FM 사용하여 날짜의 '0' 표기 생략 가능

SELECT TO_CHAR(COL4, 'YYYY-MM-DD HH24:MI:SS') FROM TAB3; 
    -- 2022-08-12 13:50:00 / 2022-08-11 00:00:00
SELECT TO_CHAR(COL4, 'YYYY-FMMM-DD') FROM TAB3;
    -- 2022-8-12 / 2022-8-11

오전, 오후 값 조회
- TO_CHAR([컬럼명], 'AM') 를 세 번째 인자 없이 사용 시 Oracle의 nls_date_language 설정 형식에 맞는 데이터가 출력됨

SELECT TO_CHAR(COL4, 'PM HH:MI:SS') FROM TAB3;
    -- 오후 01:50:00 / 오전 12:00:00
SELECT TO_CHAR(COL4, 'AM', 'nls_date_language=american') FROM TAB3;
    -- PM / AM
SELECT TO_CHAR(COL4, 'AM', 'nls_date_language=korean') FROM TAB3;
    -- 오후 / 오전

요일 값 조회
- 일요일부터 '1'으로 시작하며, Oracle 언어 설정에 맞는 데이터가 출력됨

SELECT TO_CHAR(COL4, 'D') FROM TAB3;
    -- 6 / 5
SELECT TO_CHAR(COL4, 'DY') FROM TAB3;
    -- 금 / 목
SELECT TO_CHAR(COL4, 'DAY') FROM TAB3;
    -- 금요일 / 목요일

기타 조회

SELECT TO_CHAR(COL4, 'DDD') FROM TAB3; /* 224 (365일 기준 224일 째) */
SELECT TO_CHAR(COL4, 'WW') FROM TAB3; /* 32 (1년 기준 32주 째) */
SELECT TO_CHAR(COL4, 'Q') FROM TAB3; /* 3 (3분기) */
SELECT TO_CHAR(COL4, 'MON') FROM TAB3; /* 8월 */
SELECT TO_CHAR(COL4, 'DL') FROM TAB3; /* 2022년 8월 12일 금요일 */

1-2-3. 날짜 데이터

SYSDATE : 년월일시분초 표현
SYSTIMESTAMP : 밀리세컨드까지 표현 가능
- 컬럼 데이터 타입을 TIMESTAMP로 지정해야 사용 가능

SELECT SYSDATE FROM DUAL; /* 22/08/18 */
SELECT TO_CHAR(SYSDATE, 'YYYY.MM.DD HH24:MI:SS') FROM DUAL; /* 2022.08.18 11:34:44 */
SELECT SYSTIMESTAMP FROM DUAL; /* 22/08/18 11:47:08.541000000 +09:00 */
SELECT TO_CHAR(SYSTIMESTAMP, 'YYYY.MM.DD HH24:MI:SS:FF') FROM DUAL; /* 2022.08.18 11:47:52:329000 */

날짜를 더하거나 뺄 수 있음

SELECT SYSDATE + 1 FROM DUAL; /* 22/08/19 */
SELECT TO_DATE('1997.10.16 15:00:30', 'YYYY.MM.DD HH24:MI:SS') + 3 FROM DUAL; /* 97/10/19 */
SELECT TO_CHAR(SYSDATE - 1/24 * 2, 'YYYY.MM.DD HH24:MI:SS') FROM DUAL; /* 2022.08.18 09:46:11 (2시간 전) */
SELECT TO_CHAR(SYSDATE + 1/24/60, 'YYYY.MM.DD HH24:MI:SS') FROM DUAL; /* 2022.08.18 11:45:32 (1분 뒤) */
SELECT TO_CHAR(SYSDATE + 1/24/60/60 * 8, 'YYYY.MM.DD HH24:MI:SS') FROM DUAL; /* 2022.08.18 11:45:32 (8초 뒤) */