[펌] 오라클 데이터 형 변환 함수

출처 : http://pengs.pe.kr/174


3.데이터 형 변환 함수(conversion function)
3-1) ASCIISTR 함수
3-2) BIN_TO_NUM 함수
3-3) CAST 함수
3-4) CHARTOROWID 함수
3-5) COMPOSE 함수 
3-6) CONVERT 함수
3-7) HEXTORAW 함수
3-8) NUMTODSINTERVAL 함수
3-9) NUMTOYMINTERVAL 함수
3-10) RAWTOHEX 함수
3-11) RAWTONHEX 함수
3-12) ROWIDTOCHAR 함수
3-13) ROWIDTONCHAR 함수
3-14) TO_CHAR(character) 함수
3-15) TO_CLOB 함수
3-16) TO_DSINTERVAL 함수
3-17) TO_LOB 함수
3-18) TO_MULTI_BYTE 함수
3-19) TO_NCHAR(character) 함수
3-20) TO_NCHAR(datetime) 함수
3-21) TO_NCHAR(number) 함수
3-22) TO_NCLOB 함수
3-23) TO_NUMBER 함수
3-24) TO_SINGLE_BYTE 함수
3-25) TO_YMINTERVAL 함수
3-26) TRANSLATE ... USING 함수
3-27) UNISTR 함수

3-1) ASCIISTR 함수
--------------------------------------------------------------------------------
asciistr('string')의 string의 아스키 문자로 반환한다.
A
【예제】
SQL> select ascii('ABACDE') from dual;
☜ ABACDE의 두번째 A는 A에 움라우트(Umlaut)가 붙은 글씨이다.
ASCIIS
------
ABDCDE
           
SQL>


3-2) BIN_TO_NUM 함수
--------------------------------------------------------------------------------
이 함수는 2진수 벡터를 10진수로 변환한다.
SQL> select bin_to_num(1,0,1,0) from dual;
BIN_TO_NUM(1,0,1,0)
-------------------
                 10
SQL>

3-3) CAST 함수
--------------------------------------------------------------------------------
데이터형식이나 collection 형식을 다른 데이터형식이나 다른 collection 형식으로 변환한다.
【예제】데이터형식인 경우
SQL> select current_date from dual;
CURRENT_D
---------
30-JUL-04
SQL> select cast(current_date as timestamp) from dual;
CAST(CURRENT_DATEASTIMESTAMP)
---------------------------------------------------------------------------
30-JUL-04 12.29.15.000000 PM
SQL>

3-4) CHARTOROWID 함수
--------------------------------------------------------------------------------
이 함수는 char, varchar2, nchar, ncharvar2형 데이터 타입을 rowid 형 데이터 타입으로 변경한다.
【예제】
SQL> select name from emp
  2  where rowid = chartorowid('AAAHZ+AABAAAMWiAAF');
NAME
----------
jijoe
SQL> select rowid,name from emp;
ROWID              NAME
------------------ ----------
AAAHZ+AABAAAMWiAAA Cho
AAAHZ+AABAAAMWiAAB Joe
AAAHZ+AABAAAMWiAAC kim
AAAHZ+AABAAAMWiAAF jijoe
SQL>

여기서 rowid의 의미는 다음과 같다.
AAAHZ+  AAB  AAAMWi  AAA 객체번호  테이블스페이스번호  블록번호  행번호


3-5) COMPOSE 함수
--------------------------------------------------------------------------------
입력된 스트링을 unicode로 나타낸다.
【예제】
SQL> select compose('aa' || unistr('308') ) from dual;
CO
--
aa
SQL>

3-6) CONVERT 함수
--------------------------------------------------------------------------------
입력된 문자열을 지정한 코드로 변환한다.
공용 문자셋은 살펴보자.
US7ASCII  US 7-bit ASCII 문자 WE8DEC  서유럽 8비트 문자 WE8HP  HP 서유럽 레이져젯 8비트 문자 F7DEC  DEC 프랑스 7비트 문자 WE8EBCDIC500  IBM 서유럽 EBCDIC 코드 페이지 500 WE8PC850  IBM PC 코드 페이지 850 WE8ISO8859P1  ISO 8859 서유럽 8비트 문자
【예제】
SQL> select convert('arirang','we8pc850') from dual;
CONVERT
-------
arirang
SQL>

3-7) HEXTORAW 함수
--------------------------------------------------------------------------------
HEXTORAW(char) 함수는 char, varchar2, nchar, nvarchar2 따위의 문자로 주어지는
hexadecimal digit을 raw 값으로 변환한다.
【예제】
SQL> create table test(raw_col RAW(10));
Table created.
SQL> insert into test VALUES (HEXTORAW('7D'));
1 row created.
SQL> select * from test;
RAW_COL
--------------------
7D
SQL>


3-8) NUMTODSINTERVAL 함수
--------------------------------------------------------------------------------
NUMTODSINTERVAL(n,'char_expr') 함수는 n을 interval day to second로 변환하여 출력한다.
char_expr은 다음 중의 하나이다.
  ‘DAY’
  ‘HOUR’
  ‘MINUTE’
  ‘SECOND’
【예제】
SQL> select numtodsinterval(100,'MINUTE') from dual;
NUMTODSINTERVAL(100,'MINUTE')
--------------------------------------------------------------------------
+000000000 01:40:00.000000000
SQL>
【예제】
SQL> connect scott/tiger
Connected.
SQL> select ename, hiredate,
  2  numtodsinterval(100,'day')+hiredate from emp;
ENAME      HIREDATE  NUMTODSIN
---------- --------- ---------
SMITH      17-DEC-80 27-MAR-81
ALLEN      20-FEB-81 31-MAY-81
WARD       22-FEB-81 02-JUN-81
JONES      02-APR-81 11-JUL-81
MARTIN     28-SEP-81 06-JAN-82
BLAKE      01-MAY-81 09-AUG-81
CLARK      09-JUN-81 17-SEP-81
SCOTT      19-APR-87 28-JUL-87
KING       17-NOV-81 25-FEB-82
TURNER     08-SEP-81 17-DEC-81
ADAMS      23-MAY-87 31-AUG-87
JAMES      03-DEC-81 13-MAR-82
FORD       03-DEC-81 13-MAR-82
MILLER     23-JAN-82 03-MAY-82
14 rows selected.
SQL>


3-9) NUMTOYMINTERVAL 함수
--------------------------------------------------------------------------------
NUMTOYMINTERVAL(n,'char_expr') 함수는 n을 interval year to month로 변환하여 출력한다.
char_expr은 다음 중의 하나이다.
  ‘YEAR’
  ‘MONTH’
【예제】
SQL> select numtoyminterval(30,'month') from dual;
NUMTOYMINTERVAL(30,'MONTH')
---------------------------------------------------------------------------
+000000002-06
SQL>
【예제】
SQL> connect scott/tiger
Connected.
SQL> select ename,hiredate,
  2  numtoyminterval(30,'month')+hiredate from emp;
ENAME      HIREDATE  NUMTOYMIN
---------- --------- ---------
SMITH      17-DEC-80 17-JUN-83
ALLEN      20-FEB-81 20-AUG-83
WARD       22-FEB-81 22-AUG-83
JONES      02-APR-81 02-OCT-83
MARTIN     28-SEP-81 28-MAR-84
BLAKE      01-MAY-81 01-NOV-83
CLARK      09-JUN-81 09-DEC-83
SCOTT      19-APR-87 19-OCT-89
KING       17-NOV-81 17-MAY-84
TURNER     08-SEP-81 08-MAR-84
ADAMS      23-MAY-87 23-NOV-89
JAMES      03-DEC-81 03-JUN-84
FORD       03-DEC-81 03-JUN-84
MILLER     23-JAN-82 23-JUL-84
14 rows selected.
SQL>



3-10) RAWTOHEX 함수
--------------------------------------------------------------------------------
RAWTOHEX(raw) 함수는 raw 값을 hexadecimal 값으로 변환한다.
【예제】
SQL> create table test(raw_col RAW(10));
Table created.
SQL> insert into test VALUES (HEXTORAW('7D'));
1 row created.
SQL> select * from test;
RAW_COL
--------------------
7D
SQL> select rawtohex(raw_col) from test;
RAWTOHEX(RAW_COL)
--------------------
7D
SQL>

3-11) RAWTONHEX 함수
--------------------------------------------------------------------------------
RAWTONHEX(raw) 함수는 raw 값을 nvarchar2형 hexadecimal 값으로 변환한다.
【예제】
SQL> create table test(raw_col RAW(10));
Table created.
SQL> insert into test VALUES (HEXTORAW('7D'));
1 row created.
SQL> select * from test;
RAW_COL
--------------------
7D
SQL> select rawtonhex(raw_col) from test;
RAWTONHEX(RAW_COL)
--------------------
7D
SQL>

3-12) ROWIDTOCHAR 함수
--------------------------------------------------------------------------------
RAWIDTOCHAR(rowid) 함수는 rowid 값을 varchar2 형식의 데이터로 변환한다.
【예제】
SQL> select rowid from test;
ROWID
------------------
AAAHbHAABAAAMXCAAA
SQL> select rowid from test
  2  where rowidtochar(rowid) like '%AABAA%';
ROWID
------------------
AAAHbHAABAAAMXCAAA
SQL>
여기서 rowid의 의미는 다음과 같다.
AAAHbH  AAB  AAAMXC  AAA 객체번호  테이블스페이스번호  블록번호  행번호


3-13) ROWIDTONCHAR 함수
--------------------------------------------------------------------------------
RAWIDTONCHAR(rowid) 함수는 rowid 값을 nvarchar2 형식의 데이터로 변환한다.
【예제】
SQL> select rowid from test;
ROWID
------------------
AAAHbHAABAAAMXCAAA
SQL> select rowid from test
  2  where rowidtochar(rowid) like '%AABAA%';
ROWID
------------------
AAAHbHAABAAAMXCAAA
SQL> select lengthb(rowidtonchar(rowid)), rowidtonchar(rowid)
  2  from test;
LENGTHB(ROWIDTONCHAR(ROWID)) ROWIDTONCHAR(ROWID
---------------------------- ------------------
                          36 AAAHbHAABAAAMXCAAA
SQL>

여기서 rowid의 의미는 다음과 같다.
AAAHbH  AAB  AAAMXC  AAA 객체번호  테이블스페이스번호  블록번호  행번호


3-14) TO_CHAR(character) 함수
--------------------------------------------------------------------------------
이 함수는 nchar, nvarchar2, clob, nclob 형식의 데이터를
데이터베이스 character set으로 변환한다. 즉, 문자로 변환한다.
【형식】
TO_CHAR( nchar| clob | nclob)
【예제】
SQL> select to_char('01110') from dual;
TO_CH
-----
01110
SQL>


3-15) TO_CLOB 함수
--------------------------------------------------------------------------------
이 함수는 LOB 컬럼에 있는 NCLOB나 또는 다른 문자 스트링을 CLOB로 변환한다.
【형식】
TO_CLOBR({ lob_column | char})
【예제】
SQL> select to_clob('corea') from dual;
TO_CLOB('COREA')
--------------------------------------------------------------------------
corea
SQL>


3-16) TO_DSINTERVAL 함수
--------------------------------------------------------------------------------
이 함수는 문자열을 interval day to second 형식으로 변환하는 함수이다.
【형식】
to_dsinterval ( char [ ‘nlsparam’] )
【예제】
SQL> select sysdate,
  2  sysdate+to_dsinterval('003 17:00:00') as "3days 17hours later"
  3  from dual;
SYSDATE   3days 17h
--------- ---------
04-AUG-04 08-AUG-04
SQL>

3-17) TO_LOB 함수
--------------------------------------------------------------------------------
TO_LOB(long_column) 함수는 LONG, LONG RAW 컬럼의 데이터를 LOB 값으로 변환한다.
【예제】
SQL> create table test2(zz clob);
Table created.
SQL> insert into test2
  2  (select to_lob(p.raw_col) from test p);
SQL>

3-18) TO_MULTI_BYTE 함수
--------------------------------------------------------------------------------
TO_MULTI_BYTE(char) 함수는 싱글 바이트 문자를 포함한 모든 문자열을 다중바이트 문자열로 변환한다.
【예제】
SQL> select dump(to_multi_byte('Corea')) from dual;
DUMP(TO_MULTI_BYTE('COREA'))
-----------------------------------------------------
Typ=1 Len=10: 163,195,163,239,163,242,163,229,163,225
SQL>


3-19) TO_NCHAR(character) 함수
--------------------------------------------------------------------------------
이 함수는 문자스트링, clob, nclob 형식의 데이터를 national character set,
즉 nchar으로 변환한다. 이는 translate ... using 문의 경우와 같다.
【형식】
TO_NCHAR({char|clob|nclob} [,fmt [,'nlsparam']])
【예제】
SQL> select to_nchar('Corea') from dual;
TO_NC
-----
Corea
SQL>

3-20) TO_NCHAR(datetime) 함수
--------------------------------------------------------------------------------
이 함수는 date, timestamp, timestamp with time zone, timestamp with local time zone,
interval month to year, interval day to second 형식의 데이터를
nchar 형식의 데이터로 변환한다.
【형식】
TO_NCHAR({datetime|interval} [,fmt [,'nlsparam']])
【예제】
SQL> select to_nchar(sysdate) from dual;
TO_NCHAR(SYSDATE)
------------------------------
05-AUG-04
SQL>


3-21) TO_NCHAR(number) 함수
--------------------------------------------------------------------------------
이 함수는 숫자를 nvarchar2 형식의 데이터로 변환한다.
【형식】
TO_NCHAR(n [,fmt [,'nlsparam']])
【예제】
SQL> select to_nchar(1234) from dual;
TO_N
----
1234
SQL> select to_nchar(rownum) from test;
TO_NCHAR(ROWNUM)
----------------------------------------
1
SQL>


3-22) TO_NCLOB 함수
--------------------------------------------------------------------------------
이 함수는 clob, 문자열 형식의 데이터를 nclob 형식의 데이터로 변환한다.
【형식】
TO_NCLOB({char|lob_column})
【예제】
SQL> select to_nclob('Corea') from dual;
TO_NCLOB('COREA')
--------------------------------------------------------------------------
Corea
SQL>


3-23) TO_NUMBER 함수
--------------------------------------------------------------------------------
이 함수는 숫자를 포함하는 char, varchar2, nchar, nvarchar2 형식의
문자 데이터를 number 형식의 숫자 데이터로 변환한다.
【형식】
TO_NUMBER(char [,fmt [,'nlsparam']])
【예제】
SQL> select to_number('1234') from dual;
TO_NUMBER('1234')
-----------------
             1234
SQL>

3-24) TO_SINGLE_BYTE 함수
--------------------------------------------------------------------------------
TO_SINGLE_BYTE(char) 함수는 다중 바이트 문자열을 single byte 문자로 변환한다.
【예제】
SQL> select dump(to_multi_byte('Corea')) from dual;
DUMP(TO_MULTI_BYTE('COREA'))
-----------------------------------------------------
Typ=1 Len=10: 163,195,163,239,163,242,163,229,163,225
SQL> select dump(to_single_byte('Corea')) from dual;
DUMP(TO_SINGLE_BYTE('COREA'))
------------------------------
Typ=1 Len=5: 67,111,114,101,97
SQL> select to_single_byte(chr(65)) from dual;
T
-
A
SQL>


3-25) TO_YMINTERVAL 함수
--------------------------------------------------------------------------------
TO_YMINTERVAL ( char ) 함수는 문자열을 interval year to month 형식으로 변환하는 함수이다.
【예제】
SQL> select sysdate,
  2  sysdate+to_yminterval('01-03') "15Months later"
  3  from dual;
SYSDATE   15Months
--------- ---------
04-AUG-04 04-NOV-05
SQL>

3-26) TRANSLATE ... USING 함수
--------------------------------------------------------------------------------
이 함수는 텍스트 형식의 데이터를 지정한 문자 형식의 데이터로 변환한다.
【형식】
TRANSLATE ( text USING {CHAR_CS|NCHAR_CS} )
【예제】
SQL> select translate('Corea' USING char_cs) from dual;
TRANS
-----
Corea
SQL> select to_nchar('Corea') from dual;
TO_NC
-----
Corea
SQL>

3-27) UNISTR 함수
--------------------------------------------------------------------------------
UNISTR('string') 함수는 스트링 형식의 데이터를 nchar 형식의 데이터로 변환한다.
【예제】
SQL> select unistr('abc0e50f10f6') from dual;
UNISTR
------
abc??o
SQL> select unistr('Corea') from dual;
UNIST
-----
Corea
SQL>