데이터 타입을 변환하는 목적을 가진 함수가 변환 함수이다.
숫자, 문자, 날짜 등 간의 변환을 하겠다는 의미이다.
to_char 는 숫자를 문자로, 날짜를 문자로 함수는 문자로의 타입 변환 함수이다.
1) 숫자 → 문자: 숫자의 출력 포맷 변경
보통 금액을 표기할 때 1,000원과 같이 숫자 포맷에 (,)가 있는 경우가 있다.
0 또는 9로 채울 수 있으나 이 둘은 차이가 있다.
아래처럼 실습을 해보면 알 수 있다.
9로 채우는 경우는 부족한 자리가 공백으로 채워지나 0으로 채우는 경우 부족한 자리가 0으로 채워진다.
뒤에 .99를 붙이는 경우는 뒷자리가.00으로 표시되어 나오며 $는 앞에 달러 단위가 붙은 채 출력된다.
그리고 표현하려는 포맷의 자리수가 원본보다 작은 경우에는 데이터가 *으로 표현된다.
2) 날짜 → 문자
날짜의 일부 추출이 가능한데, 왜 to_char를 사용하여 문자로 리턴을 해야만 하는가?
컬럼 자체가 날짜 컬럼이면 DBMS의 날짜에 영향을 받는다. 그리고 orange툴의 디폴트 날짜 포맷에 따라 리턴되기 때문에 날짜 데이터면 무조건 orange에 맞춰지게 된다.
문자라면 orange 툴을 거쳐도 형식 변경이 되지 않는다. 그러한 영향을 받지 않기 위하여 문자로 리턴하는 것이다.
MM/DD-YYYY 처럼 이렇게 날짜 포맷 변경이 가능하다.
그렇다면 날짜 포맷에는 어떤 것이 있을까?
YYYY/YY: 연도
RRRR: 연도
MM/MONTH( 문자 이름으로 출력)/MON(축약형 출력): 월
DD: 일
HH12/HH24: 시
MI: 분
SS: 초
DAY: 요일(날짜 언어에 따라 영문/한글 출력)
alter session set nls_date_language = 'american'; / 'korean'
Q: 분기
W: N주차
ddth/spth 일의 서수 표현 / 일의 서수 표현 영문으로
위의 YYYY와 RRRR의 차이는 무엇인가?
RR은 Y2K 이전 날짜를, YY는 Y2K 이후 날짜 연도를 리턴한다.
아래에서 중요한 이 부분에 대한 두 자리 연도 인식을 설명하도록 하고 일단 짧게 설명하고 넘어가겠다.
그럼 간단히 테스트를 해보면 다음과 같다.
YYYY는 날짜 중 일부인 연도를 추출한 것이고, MM/DD-YYYY 는 날짜 포맷을 변경한 것이다.
Q, W, day도 테스트 해보았다.
영문명인 경우, day와 DAY는 각각 소문자, 대문자로 출력이 되며 이는 month/MONTH도 똑같이 적용된다.
다음은 to_number가 있다.
to_number(대상[, 포맷]) 의 형태로 작성한다.
들어가기에 앞서
select 1+'1'
from dual;
을 하는 경우 출력이 될까? 이는 2라고 출력이 된다. 문자와 숫자의 합인데 어떻게 출력이 되는 것일까?
이는 다른 언어에서는 허용되지 않으나 SQL에서는 연산이 된다. 묵시적 형변환이 일어난 것이다.
이 부분에 대하여 실수할 수는 있으나 성능에 문제가 생긴다.
to_number는 이렇게 간단히 하고 넘기도록 하겠다.
to_date(대상[, 포맷])
여기서 포맷은 대상의 형태를 정의하기 위함이며 변경 목적이 아니다.
포맷을 생략하게 되면 현 DBMS의 날짜 포맷에 맞게 해석하게 된다.
그러므로 현 DMBS의 날짜 포맷과 일치하는 대상일 경우에는 포맷을 생략할 수 있다.
select to_date('2023/01/01') + 100
from dual;
이렇게 하게 되면 100일 뒤를 계산할 수 있다.
2023년 1월 1일을 아래처럼 출력해보자.
첫 번째 줄처럼 출력하면 2001년 1월 23일이 출력이 된다.
그렇기 때문에 두 번째 줄처럼 뒤에 포맷을 작성하는 것이다.
★ 그러나 출력 포맷이 아니라는 것에 주의를 해야한다. 출력 자체를 저렇게 하고 싶다면 to_char를 사용하면 된다.
예제를 풀어가며 설명을 계속 하겠다.
STUDENT 테이블에서 3학년 학생의 이름, 생년월일, 전화번호를 출력하여라. 단 생년월일은 주민번호를 사용하여 출력하되, 다음과 같은 형식으로 출력하여라.
7510231901813 → 10/23, 1973
이렇게 풀이하였다.
아래 선생님 풀이를 살펴보면 단계적으로 풀이가 되었으며,
★ YY과 RR의 차이점이 분명히 들어나있으니 꼭 참고하도록 한다.
★ 두 자리 연도 인식 ★
위 실습에서와 같이
YY: Y2K이후, 2000년대
RR: 50이후부터 1900년대를 리턴한다 / 49까지 2000년도를 리턴한다.
언어마다 분기점이 다르긴 하지만 SQL은 정확히 반 지점이다.
실수하지 말아야 할 것은 YYYY가 아니라 YY 이렇게 두 자리 연도인 것이다.
나는 YYYY로 출력하였기에 여기에 영향 받지 않은 채로 출력하였다.
또 중요한 예제가 하나 있다.
STUDENT 테이블에서 토요일에 태어난 학생 이름, 학년, 태어난 요일을 출력하며 birthday 컬럼을 사용해서 풀이하여라.
실습은 언어를 한글로 바꾸어서 진행할 것(영문은 왜 당장 안되는지가 포인트임★)
왜 영어가 아닌 한국어로 바꾸라고 하였을까?
영어로 바꿔서 진행해보겠다.
출력이 되지 않는다!
왜냐하면 공백이 있기 때문이다.
영어의 요일 알파뱃의 길이는 제각각이어서 가장 긴 문자에 맞추어져있기 때문에 이에 비하여 짧은 요일 단어들에는 공백이 생긴다.
따라서 다음과 같이 공백 제거 후 검색이 가능하다.
한글은 모든 요일의 글자 길이가 같아서 공백의 문제가 없었다.
이와 같은 맥락으로 MONTH도 문제가 될 것이므로 주의하자.
예제 하나 더 풀어보자.
emp 테이블에서 10번 부서직원의 이름, 부서번호, 급여 검토일과 요일을 출력하여라.
단, 급여 검토일은 입사일보다 100일 후라 가정한다.
또 다른 중요한 예제이다.
이번 글에서는 예제에 중요한 것이 많다.
PROFESSOR 테이블에서 1985년 11월 30일에 입사한 교수의 이름, 입사일을 출력하여라.
단, 입사일은 다음과 같은 형식으로 출력하여라. (NOV-30, 1985)
이렇게 풀었는데, 선생님은 다음과 같이 다시 풀어주셨다.
여기서 주목할 해설은 sol2 다. 성능과 직결되어있기 때문이다.
성능 관련한 index를 설명해주셨는데, index 개념은 나중에 알려주신다고 하셨고,
index 스캔할 때 중요한 점은 절대 검색 조건(검색 대상)에 어떠한 변형도 일으키지 않아야 한다는 것이다.
그렇기 때문에 sol2가 성능에 이점이 있다.
sol1은 index scan 고려시 성능에 저하가 온다.
따라서 sol2럼 상수를 변경해야 할 것이다.★
그렇기 때문에 내가 푼 방법은 검색 대상을 변경하였기 때문에 성능에 좋지 않다는 것을 알았다.
이와 관련한 예시 하나를 더 풀어보자.
emp테이블에서 연봉 10% 인상했을 시 3000이 넘는 행을 출력하여라.
둘 다 같은 값이 출력되지만 sol2 처럼 풀도록 하자.
20라인과 100 라인 중에 100라인 성능이 좋다면 100라인이 좋은 쿼리이다.
마지막 일반 함수는 다음 글에서 작성해보겠다.
