Database/SQL

[SQL] 형 변환 함수

Untitled_Blue 2023. 5. 27. 18:26
반응형

안녕하세요. 이번 글은 형 변환 함수에 대한 설명입니다.

 

- 형 변환 함수란?

  • 형 변환 함수 : 필요에 맞게 하나의 자료형을 문자, 숫자, 날짜형의 자료형으로 바꿔주는 함수

자료형 사이의 변환 관계

다음과 같이 서로 다른 자료형으로 바꾸기 위해서는 변환 함수를 사용해야 하며 변환하고자 하는 자료형과 기존의 자료형이 무엇인지에 따라 사용해야 되는 형 변환 함수가 다르다는 점을 이해해야 한다. 함수는 크게 3가지가 있다.

  • TO_CHAR() : 날짜 또는 숫자 데이터를 문자 데이터로 변환하는 함수
  • TO_DATE() : 문자 데이터를 날짜 데이터로 변환하는 함수
  • TO_NUMBER() : 문자 데이터를 숫자 데이터로 변환하는 함수

- TO_CHAR() 함수 - '날짜에서 문자로', '숫자에서 문자로'

  • 사용 형식 : TO_CHAR(날짜, 변환하고자 하는 문자 형태)
select TO_CHAR(HIREDATE, 'YYYY.MM.DD DAY HH24:MI:SS')as "DATE_CHANGE" from EMP;

다음 코드는 EMP 테이블에서 TO_CHAR 함수를 사용해서 날짜를 '입력한 출력 형식'대로 반환해서 출력하라는 뜻을 가지고 있다. 해당 코드를 실행해보면 상단 이미지처럼 사용자가 입력한 형식대로 정상적으로 출력되는 점을 확인할 수 있다.

이때 형식을 입력할 때는 정해진 기준을 지켜야 하는데 하단 표에서 확인할 수 있다.

형식 설명
CC 세기 (기원전, 기원후 등등...)
YYYY, RRRR 연도 (4자리)
YY, RR 연도 (2자리)
MM 월 (2자리 숫자)
MON, MONTH 월 (MON : 언어별 월명 약어, MONTH : 언어별 월명 풀네임)
DD, DDD 일 (DD : 2자리 숫자, DDD : 365일 중 몇 일째인지)
DY, DAY 요일 (DY : 언어별 요일명 약어, DAY : 언어별 요일명 풀네임)
W 1년 중 몇 번째 주인지 표시 (최대 52주에서 53주)

 

+ 언어별로 설정해서 출력하기

설치한 국가에 따라 혹은 사용자가 설정해둔 언어에 따라 기본적으로 출력되는 언어는 제각각이다. 대한민국은 별도로 언어를 변경 내지 설정하지 않은 이상 기본적으로 한국어를 지원하고 있다. 하지만 한국어으로만 출력하라는 법은 없다. TO_CHAR를 통해 날짜를 출력할 때 출력형식 뒤에 'NLS_DATE_LAGNUAGE = [언어]'을 입력해주면 원하는 언어로 변환해서 출력할 수 있다. 이때 지원하는 언어는 한국어, 영어, 일본어가 있다.

select TO_CHAR(HIREDATE, 'YYYY.MM.DD DAY HH24:MI:SS', 'NLS_DATE_LANGUAGE = KOREAN')as "DATE_KOREAN",
       TO_CHAR(HIREDATE, 'YYYY.MM.DD DAY HH24:MI:SS', 'NLS_DATE_LANGUAGE = ENGLISH')as "DATE_ENGLISH",
       TO_CHAR(HIREDATE, 'YYYY.MM.DD DAY HH24:MI:SS', 'NLS_DATE_LANGUAGE = JAPANESE')as "DATE_JAPANESE"
       from EMP;

다음과 같이 원하는 언어에 맞춰서 날짜를 변환해서 출력할 수 있음을 확인 가능하다.

 

+ 숫자 구분 표시와 빈 칸 처리

형식 설명
9 공백을 채우지 않는 숫자
0 공백을 0으로 채우는 숫자
$ 달러 (미국 화폐 단위) 표시
L 설치한 국가에 맞는 단위 기호 표시
. 소수점 표시
, 1,000 단위 구분 표시

다음 표를 사용해서 형식에 맞게 숫자를 변환하여 출력할 수 있다.

select SAL, TO_CHAR(SAL, '000,000'), TO_CHAR(SAL, '999,999') from EMP;

다음 코드는 EMP 테이블에서 SAL을 출력하는데 TO_CHAR를 사용해서 천 단위로 구별하여 출력하되 하나는 빈 공간을 0을 채우고 다른 하나는 빈 자리를 채우지 않고 변환해서 출력하라는 뜻을 가지고 있다. 코드를 실행해보면 정상적으로 형식에 맞게 출력되었음을 확인할 수 있다.

select TO_CHAR(SAL, 'L999,999', 'nls_currency = ￦')as "SAL_ENGLISH",
       TO_CHAR(SAL, 'L999,999', 'nls_currency = ￥')as "SAL_JAPANESE", 
       TO_CHAR(SAL, 'L999,999', 'nls_currency = $')as "SAL_ENGLISH"
       from EMP;

다음 코드는 EMP 테이블에서 SAL를 출력하는데 빈 공간은 공백으로 두면서 L을 사용해서 지역 화폐 단위를 붙여서 출력하라는 뜻을 가지고 있다. 이때 여기서 NLS_CURRENCY를 통해서 원하는 국가의 화폐 단위로 전환해서 출력할 수 있음을 확인할 수 있다. 출력 결과를 보면 설정한 대로 화폐단위와 천 단위로 구분해서 출력된 결과를 확인할 수 있다.

- TO_DATE() 함수

  • TO_DATE(문자열, 인식될 날짜형태) : 기존 문자열을 날짜형태에 맞춰서 날짜 데이터로 변환하는 함수

이때 TO_DATE를 사용할 때 문자열과 날짜형태와 반드시 일치해야 한다.

select TO_DATE('27/05/2023', 'DD/MM/YYYY'), TO_DATE('2023-05-27', 'YYYY/MM/DD') from DUAL;

다음과 같이 문자열에서 년, 월, 일의 순서가 바뀌어져 있으면 뒤에 날짜형태도 그에 맞게 입력해야 된다는 점을 확인할 수 있으며 순서가 어떻게 되었든 상관없이 일관성 있게 년 월 일 형식으로 변환되었음을 확인 가능하다.

select * from EMP where HIREDATE > TO_DATE('27/05/1981', 'DD/MM/YYYY');

다음 코드는 EMP 테이블 내 HIREDATE가 1981년5월27일보다 늦은 날짜인 조건에 해당되는 행만 출력하라는 뜻이다.

이때 TO_DATE를 사용하지 않은 채 기존 27/05/1981를 조건에 입력했다면 ORA-01830: 날짜 형식의 지정에 불필요한 데이터가 포함되어 있습니다. 라는 오류 알림이 나오면서 정상적인 조회를 할 수 없었을 것이다. 하지만 TO_DATE를 활용해서 날짜 데이터로 변환하면 정상적으로 조건문에서 인식되면서 알맞게 출력되었음을 확인할 수 있다.

- TO_NUMBER() 함수

  • TO_NUMBER(문자열, 인식될 숫자형태) : 문자열을 숫자형태로 인식해서 숫자 데이터로 변환하는 함수

TO_DATE 함수와 마찬가지로 문자열과 숫자형태가 일치해야 한다.

select TO_NUMBER('2023', '0000'), TO_NUMBER('2023', '99999') from EMP;

다음은 TO_NUMBER 함수를 사용해서 기존 문자 데이터를 숫자 데이터로 변환하는 코드 및 실행 결과이다. 이때 99999를 입력한 것에 대해 불일치한 것이 아니냐는 생각이 들 수 있다. 이는 9라는 형식 자체가 빈 값을 채우지 않는다는 특성이 있기 때문에 문자 데이터 이상의 갯수를 입력해도 오류가 없기 때문이다.

select * from EMP where SAL > TO_NUMBER('1,000', '9,999');

다음 코드는 EMP 테이블에서 TO_NUMBER를 사용해서 SAL(급여)를 1000보다 많이 받는 모든 칼럼에 대한 행을 출력하라는 뜻을 가지고 있다. 실행해보면 1000보다 많이 받는 사람들만 정상적으로 출력되는 점을 확인할 수 있다.

select * from EMP where SAL > '1,000';

이때 만약 TO_NUMBER 함수를 사용하지 않은 채 문자 데이터 형식은 1,000을 그대로 사용하게 되면 ORA-01722: 수치가 부적합합니다 라는 오류가 발생하게 된다. 이처럼 기호가 있는 문자 데이터를 숫자 데이터로 인식하기 위해서는 TO_NUMBER 함수를 반드시 사용해야 된다는 점을 알 수 있다.

 

다음 글은 함수 시리즈의 마지막인 DECODE와 CASE 조건문에 대한 설명입니다.

반응형