[불친절한SQL] 기초쿼리, 변환 함수

○ 변환함수

값의 데이터 타입을 다른 데이터 타입으로 변환하는 함수이다.

 

TO_CHAR(number) 함수

숫자 값 n을 fmt형식의 문자 값으로 변환한다.

TO_CHAR(n [, fmt [, 'nlsparam']])

 

자주 사용되는 포맷이다.

포맷 요소	설명
0	앞쪽이나 뒷쪽에 0을 출력
9	한자리 숫자
,	구분자
.	소수점
S	부호(양수일 경우 +, 음수일 경우 -)
G	구문자(NLS_NUMERIC_CHARACTERS으로 변경 가능)
D	소수점(NLS_NUMERIC_CHARACTERS으로 변경 가능)
$	달러
L	로컬 통화 기호
U	이중 통화 기호
C	국제 통화 기호

 

c1과 c4 열은 값이 fmt보다 커 결과가 #으로 표시된다.

SELECT TO_CHAR(12, '0'  ) AS c1, TO_CHAR(12, '00' ) AS c2
     , TO_CHAR(12, '000') AS c3, TO_CHAR(12, '9'  ) AS c4
     , TO_CHAR(12, '99' ) AS c5, TO_CHAR(12, '999') AS c6
 FROM DUAL;

 

아래와 같이 명시적 부호를 표시할 수 있다.

SELECT TO_CHAR( 1, '9') AS c1, TO_CHAR( 1, 'S9') AS c2
     , TO_CHAR(-1, '9') AS c3, TO_CHAR(-1, 'S9') AS c4
 FROM DUAL;

 

구분자, 소수점 포맷 요소를 사용한 쿼리이다.

c1열은 소수점이 반올림되었다.

SELECT TO_CHAR(1234.5, '9,999') AS c1, TO_CHAR(1234.5, '9,990.00') AS c2
  FROM DUAL;

 

TO_CHAR(datetime) 함수

datetime, interval값을 fmt 혈식의 문자 값으로 변환한다.

TO_CHAR(datetime) 함수는 반드시 포맷을 지정 해야한다.

TO_CHAR(n [, fmt [, 'nlsparam']])

 

자주 사용되는 포맷이다.

포맷 요소	설명	범위	TO_* 날짜 함수
- / , . ; :	문장부호		Y
"text"	텍스트
YYYY	년		Y
MM	월	01 ~ 12	Y
DD	일	01 ~ 31	Y
HH	시(12시간)	01 ~ 12	Y
HH24	시(24시간)	00 ~ 23	Y
MI	분	00 ~ 59	Y
SS	초	00 ~ 59	Y
FF[1..9]	소수점 이하 초		Y
AM, PM	오전, 오후		Y

 

WITH w1 AS (SELECT TO_DATE('2050-01-02 12:34:56', 'YYYY-MM-DD HH24:MI:SS') AS dt FROM DUAL)

SELECT TO_CHAR(dt, 'YYYY'                 ) AS c1
     , TO_CHAR(dt, 'YYYYMM'               ) AS c2
     , TO_CHAR(dt, 'YYYYMMDD'             ) AS c3
     , TO_CHAR(dt, 'YYYYMMDDHH24MISS'     ) AS c4
     , TO_CHAR(dt, 'YYYY-MM-DD HH24:MI:SS') AS c5
     , TO_CHAR(dt, 'HH24"H" MI"M" SS"S"'  ) AS c6
 FROM w1;

 

포맷 요소	설명	범위	TO_* 날짜 함수
Q	연중 분기		Y
WW	연중 후
DDD	년		Y
W	월	01 ~ 12	Y
SSSSS	일	01 ~ 31	Y

WITH w1 AS (SELECT TO_DATE('2050-04-15 12', 'YYYY-MM-DD HH24') AS dt FROM DUAL)

SELECT TO_CHAR(dt, 'YYYY-Q'  ) AS c1, TO_CHAR(dt, 'YYYY-WW' ) AS c2
     , TO_CHAR(dt, 'YYYY-DDD') AS c3, TO_CHAR(dt, 'YYYYMM-W') AS c4
     , TO_CHAR(dt, 'SSSSS')    AS c5
  FROM w1;

 

포맷 요소	설명	TO_* 날짜 함수
MONTH	월 이름	Y
MON	월 약자
DAY	요일 이름	Y
DY	요일 약자	Y
D	요일 숫자(NEXT_DAY 함수 숫자 형식과 동일한 값을 반환)	Y

WITH w1 AS (SELECT DATE '2050-01-01' AS dt FROM DUAL)
SELECT TO_CHAR(dt, 'MON') AS c1, TO_CHAR(dt, 'MONTH') AS c2
     , TO_CHAR(dt, 'DY' ) AS c3, TO_CHAR(dt, 'DAY'  ) AS c4
     , TO_CHAR(dt, 'D')   AS c5
  FROM w1;

 

TO_NUMBER 함수

fmt형식의 expr을 숫자 값으로 변환한다.

TO_NUMBER(expr [, fmt [, 'nlsparam']])

 

위쪽 쿼리는 문자 값에 구분자(,)가 포함되어 있어 에러가 발생한다.

SELECT TO_NUMBER('+1,234.50') AS c1 FROM DUAL; --에러
SELECT TO_NUMBER('+1,234.20', 'S9999,990.00') AS c2 FROM DUAL;

 

TO_DATE 함수

fmt형식의 char를 DATE 타입으로 변환한다.

TO_CHAR 함수처럼 반드시 포맷을 지정해야 한다.

TO_DATE함수는 fmt이 정확하게 일치하지 않아도 데이터가 변환이 되지만

SELECT TO_DATE('2005'          , 'YYYY')             AS c1
     , TO_DATE('200501'        , 'YYYYMM')           AS c2
     , TO_DATE('20050102'      , 'YYYYMMDD')         AS c3
     , TO_DATE('20050102123456', 'YYYYMMDDHH24MISS') AS c4
  FROM DUAL;

 

FX 포맷 한정자를 사용하면 fmt이 정확히 일치해야 데이터가 변환된다.

포맷 한정자	설명
FX	char와 fmt이 정확히 일치해야 한다.(Format eXact)

SELECT TO_DATE('2050-1-2 3:4:5', 'FXYYYY-MM-DD HH24:MI:SS') AS c1 FROM DUAL;
--ORA-01862: 수치 값이 포맷 항목의 길이와 일치하지 않습니다.

 

TO_TIMESTAMP 함수

fmt형식의 char를 TIMESTAMP 값으로 변환한다.

TO_TIMESTAMP(char [, fmt [, 'nlsparam']])

 

SELECT TO_TIMESTAMP('2050-01-02 12:34:56.789', 'YYYY-MM-DD HH24:MI:SS.FF3') AS c1 FROM DUAL;

 

CAST 함수

expr을 type_name에 지정한 데이터 타입으로 변환한다. fmt는 12.2 버전부터 사용할 수 있다.

NUMBER, DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE,

TIMESTAMP WITH LOCAL TIME ZONE을 지정할 수 있다.

SELECT CAST('123'                        AS VARCHAR2(5)                ) AS c1
     , CAST('123.456'                    AS NUMBER(6,3)                ) AS c2
     , CAST('2050-01-02'                 AS DATE                       ) AS c3
     , CAST('2050-01-02 12:34:56'        AS TIMESTAMP(2)               ) AS c4
     , CAST('2050-01-02 12:34:56 +08:00' AS TIMESTAMP(2) WITH TIME ZONE) AS c5
  FROM DUAL;

 

fmt를 사용한 쿼리다.

SELECT CAST('+1,234.50' AS NUMBER(7,2), 'S999,990.00' ) AS c1 FROM DUAL;