#5 5일차_날짜 파싱 시 유의사항, 변환 함수, NULL 치환 함수

5일차에 배운 내용을 정리하였다.

 목차는 다음과 같다. :

 

01 날짜 파싱 시 유의사항

 

02 변환 함수

 -01 to_number

  1) 숫자 -> 문자

  2) 문자 -> 숫자

  -02 to_char

  -03 to_date

 

03 null 치환 함수

 -02 nvl2

 -03 coalesce

 -04 nullif

 

---

01 날짜 파싱 시 유의사항

 두 자리 연도에 대한 해석을 1900년도 또는 2000년도로 각각 해석하기 위한 포맷이 존재한다.

 

 1) RR(Round year / Rolling year)
  두 자리 연도가 1~49인 경우 : 2000년대
  두 자리 연도가 50~99 : 1900년대

  ex) 99년 -> 1999년

        01년 -> 2001년

 

select to_date('26/03/11', 'yy/mm/dd') date1,
       to_date('01/03/11', 'rr/mm/dd') date2,
       to_date('99/03/11', 'rr/mm/dd') date3,
       to_date('49/03/11', 'rr/mm/dd') date4,
       to_date('50/03/11', 'rr/mm/dd') date5
  from dual;

 

 

  2) YY(Year, last 2 digits) : 2000년대
  2000년대로 모두 취급

 

  * 외우기 번거롭다면 1900년대 데이터는 rr을, 2000년대 데이터는 yy로 이용한다 정도로 이해해도 괜찮다.

 

 만약 정말 1948년에 대한 해석이 필요할 시, 2자리로 표기한 년도를 rr, yy를 사용하여 해석하는 것보다 4자리로 맞추는 것이 더 났다. (rr, yy인지까지 고려하면 복잡해지는 경우가 많다.)

  => 두 자리 연도에 대한 해석이 되지 않도록 아예 4자리 연도로 바꾼 후 to_date를 통해 날짜변환을 하는 것이 좋다.

 

select to_date('49/12/25', 'rr/mm/dd'),
       to_date('49/12/25', 'yy/mm/dd'),
       to_date('19'||'49/12/25', 'yyyy/mm/dd')
  from dual;

 

---

02 변환 함수

데이터 타입을 변환하기 위해 사용한다.

숫자 (3) -> 문자('03')

문자('03') -> 숫자(3)

문자 -> 날짜

 

 -01 to_number

 to_number(대상)

 

  문자와 숫자를 비교할 시, 연산과정에서 문자데이터를 숫자타입으로 자동으로 변환한다.

이를 묵시적 형 변환이라고 하며, 결과 출력은 정상적으로 하나 성능이 떨어지기에 데이터타입을 맞추는 것이 중요하다.

 

select '100' + 1      
  from dual; 

 

select to_number('100' + 1)   
  from dual; 

 

 

---

 -02 to_char

1) 숫자 -> 문자

  to_char(대상[, 포맷])

  숫자 출력 형식을 변경할 때 사용한다.

  ex) 1000 -> 1,000

        1000 -> $1000

        1000 -> 1000.00 (정수지이지만 실수의 형태를 강제로 취해야 할 때)

  

 포맷은 다음과 같다.

 - 9 : 한자리 숫자를 표현, 부족한 자릿수를 공백으로 채움

 - 0 : 한자리 숫자를 표현, 부족한 자릿수를 공백으로 채움

 - , : 천 단위 구분기호

 - & : 달러기호

 - . : 소숫점 표현기호

 

select to_char(100)
  from dual;

 

 

  원본 크기보다 포맷이 작은 자릿수면 출력이 되지 않으며, 천 단위 구분기호 같은 경우에도 ',' 위치를 직접 지정해야 한다.

  한 자리 숫자를 표현하는 0과 9의 경우 원본 크기 < 포맷일 시 남는 길이를 추가적으로 0으로 출력하냐 아니냐의 차이가 있다.

 또한 to_char를 통한 치환의 경우 앞에 공백이 하나씩 발생하며, 이를  제거하고 싶을 시 trim 함수를 이용하면 된다.

 

select to_char(100, '9999'),
       to_char(100, '0000'),
       to_char(100, '0999')
  from dual;

 

 

  치환하려는 대상 앞의 길이는 0으로 채워지느냐 그렇지 않느냐의 차이지만, 소숫점 부분은 0과 9의 사용에 무관하게 무조건 0으로 출력된다.

 

select to_char(100, '$999.99'),
       to_char(100, '$000.00')
  from dual;

 

---

 -03 to_date

  날짜의 출력 형식을 변경한다.

  ex) '2026/03/11' -> '11/03/2026' 또는 '2026'

 

select to_date(20260311, 'yyyymmdd'),      -- 숫자 -> 날짜(추천X, 파이썬에서는 불가)
       to_date('2026-03-11', 'yyyy-mm-dd')     -- 문자 -> 날짜
  from dual;

 

 

+) 일 시간 분
 30분 후의 시간 구하기
 
 select sysdate + 30*(1/24/60)
   from dual;

 

 

alter session set nls_date_format = 'mm/dd/yyyy';

select empno, name, emp_type, birthday
  from emp2
 where birthday > '1982/01/01';     
 
+) 지정한 월이 부족합니다 에러 발생 시
  같은 쿼리여도 날짜 포맷이 어떻게 설정되었는지를 확인해야 한다.
  그것이 아니라면 to_day를 통해 설정
 
select empno, name, emp_type, birthday
  from emp2
 where birthday > to_date('1982/01/01', 'yyyy/mm/dd'); 
 

---

03 null 치환 함수

 NULL을 포함한 산술연산(+, -, *, /) 결과는 항상 NULL을 리턴하기 때문에 NULL을 치환하여 산술연산이 가능토록 해준다.

 

 -01 nvl

nvl(대상, 치환값)

대상을 치환값으로 리턴한다.

NULL을 대상으로 지정하면 치환값으로 리턴한다.

 

일반적으로 null을 포함한 산술연산은 결과로 null을 리턴한다.

 

select 300 + null
  from dual;
   

select 300 + nvl(null, 0)
  from dual;
 

 

 그러나 nvl(null,0) 을 통해 null을 0으로 치환하였고, 300+0의 연산이 진행되어 300이라는 결과를 리턴하였음을 확인할 수 있다.

---

 -02 nvl2

nvl2(대상, null이 아닐 때 치환값, null일 때 치환값)

대상이 null이 아니라면 null이 아닐 때의 지정해준 치환값을 리턴하며, null이라면 null일 때의 치환값을 리턴한다.

null=null 리턴이 아니다.

 

 emp 테이블에서 직원별 급여(COMM)는 다음과 같다.

 

select *
  from emp;

 

 

 급여에 10을 더한 수를 추가하고 싶다면 COMM이 NULL인 직원들의 COMM+10 결과는 NULL로 반환될 것이다.

급여에 상여금을 추가한 총 급여를 구할 수 없는 상황인 것이다. 그러나 nvl2 함수를 통해 다음과 같은 결과를 얻을 수 있다.

 

select comm,
       nvl2(comm, comm+10, 0)
  from emp;

 

 

 COMM이 NULL이었던 직원들의 총 급여를 조회할 수 있다.

 또한 nvl2는 치환값 두 개가 다른 데이터 타입인 경우 하나의 데이터 타입을 가지게 된다.

 emp 테이블에서 직원의 이름, 급여, 보너스, nal2을 통한 보너스 지급 유무를 구하게 되었을 시, 'O' 와 'X'는 두 개 모두 문자 형태로 문제없이 조회가 가능하다.

 

select ename, sal, comm, nvl2(comm, 'O', 'X')
  from emp;

 

 

 그러나 문자인 'O'  대신에 숫자 0을 집어넣게 되면 숫자 타입으로 지정된 열에는 문자 타입이 입력될 수 없어 결과가 조회되지 않고 다음과 같은 오류가 발생하게 된다.

 

select ename, sal, comm, nvl2(comm, 0, 'X')     -- error
  from emp;

 

 

 그러나 문자인 'O'가 먼저 오고, 뒤에 숫자인 0이 올 시에는 결과가 정상적으로 조회된다. 두 번째 인수가 문자타입이기에 숫자의 형태도 입력 가능하기 때문이다. 따라서 어떤 데이터타입의 인수가 먼저 작성되는지를 고려해야 한다.

 

select ename, sal, comm, nvl2(comm, 'O', 0)     -- 두번째 인수가 문자타입으로 정상
  from emp;

 

---

 -03 coalesce

coalesce(대상, 값1, 값2, 값3,......)

대상을 기준으로 값을 하나씩 조회하며 null이 아닌 값 중 가장 처음에 조회되는 값을 리턴한다.

col1 -(col1이 null이면 다음 열 조회)-> col2 -( col2이 null이면 다음 열 조회 )->col3...

 

create table null_test1(
col1 number,
col2 number,
col3 number,
col4 number);

insert into null_test1 values(null, 10, 20, 30);
insert into null_test1 values(10, null, 30, 40);
insert into null_test1 values(20, 30, null, null);
insert into null_test1 values(null, null, 20, 30);
commit;

select * from null_test1;

 

 다음과 같은 테이블이 있다고 가정하였을 때 coalesce를 통해 null이 아닌 최초의 값을 반환하면 결과는 다음과 같이 나온다.

 

select coalesce(col1, col2, col3, col4)
  from null_test1;

 

---

 -04 nullif

nullif(대상, 값)

대상과 값이 일치하면 null을 리턴하며, 일치하지 않으면 원래 값을 유지한다.

 

select ename, comm, nullif(comm, 500)
  from EMP
 where deptno = 30;

 

 

 ENAME이 WARD의 경우 COMM이 500이지만, nullif(COMM, 500)은 COMM의 값이 500에 일치하기 때문에 500이 아닌 NULL를 리턴한다.

 nullif(COMM, 500)의 500은 비교대상일 뿐 출력되는 값이 아니다.

---