[OracleDB] 공통 변환함수 to_char, to_date, to_number

1. Oracle DB 에서 제공하는 변환형 함수(Conversion function)들

 

주로 아래 3가지 변환함수가 많이 사용됩니다.

Parameter	Explanation
to_char	숫자나 날짜를 문자열로 변환
to_date	문자를 날짜로 변환
to_number	문자를 숫자로 변환

기타 참조할 만한 Conversion function 으로는

-       Bin_To_Num / NumToDSInterval / NumToYMInterval  

-       To_Single_Byte / To_Multi_Byte / To_Clob / To_NClob / To_Lob

-       From_Tz / To_Timestamp / To_Timestamp_Tz / To_YMInterval / To_DSInterval

-       HexToRaw / CharToRowid / RawToHex

등이 있으나, 이번에는 언급하지 않겠습니다.

 

가능한 DB 버전 : Oracle 8i, Oracle 9i, Oracle 10g, Oracle 11g

-       아마 곧 GA release 될 Oracle 12c 에서도 지원되겠지요?

 

2. to_char function

 

Syntax : TO_CHAR(문자값,‘형식’) / to_char( value, [format_mask], [nls_language])

-       value can either be a number or date that will be converted to a string.

-       format_mask is optional. This is the format that will be used to convert value to a string.

-       nls_language is optional. This is the nls language used to convert value to a string.

 

Example : 숫자를 문자열로 변환

SQL> select TO_CHAR(350000,'$999,999') from dual ;   TO_CHAR(3 ---------  $350,000   SQL>

 

Example : 날짜를 문자열로 변환

SQL> select to_char(sysdate, 'yyyy/mm/dd HH24:MI:SS') from dual ;   TO_CHAR(SYSDATE,'YY ------------------- 2012/05/03 13:24:25   SQL> select to_char(sysdate - 31, 'yyyymmdd') from dual ;   TO_CHAR( -------- 20120402   SQL> select to_char(sysdate, 'yyyy/mm/dd') from dual ;   TO_CHAR(SY ---------- 2012/05/03   SQL> select to_char(sysdate, 'Month DD, YYYY') from dual ;   TO_CHAR(SYSDATE,'MONTHDD,YYYY') --------------------------------------------- May       03, 2012   SQL> select to_char(sysdate, 'FMMonth DD, YYYY') from dual ;   TO_CHAR(SYSDATE,'FMMONTHDD,YYYY') --------------------------------------------- May 3, 2012   SQL> select to_char(sysdate, 'MON DDth, YYYY') from dual ;   TO_CHAR(SYSDATE,'MONDDT ----------------------- MAY 03RD, 2012   SQL> select to_char(sysdate, 'FMMON DDth, YYYY') from dual ;   TO_CHAR(SYSDATE,'FMMOND ----------------------- MAY 3RD, 2012   SQL> select to_char(sysdate, 'FMMon ddth, YYYY') from dual ;   TO_CHAR(SYSDATE,'FMMOND ----------------------- May 3rd, 2012   SQL>

 

Frequently Asked Questions

Question: Why doesn't this sort the days of the week in order?       select ename, hiredate, to_char((hiredate),'fmDay') "Day"     from emp     order by "Day";   Answer: In the above SQL, the fmDay format mask used in the to_char function will return the name of the Day and not the numeric value of the day. To sort the days of the week in order, you need to return the numeric value of the day by using the fmD format mask as follows:       select ename, hiredate, to_char((hiredate),'fmD') "Day"     from emp     order by "Day";

 

3. to_date function

 

Syntax : TO_DATE(문자값, ‘형식’) / to_date(string1, [format_mask], [nls_language])

-       string1 is the string that will be converted to a date.

-       format_mask is optional. This is the format that will be used to convert string1 to a date.

-       nls_language is optional. This is the nls language used to convert string1 to a date.

 

Example : 문자를 날짜로 변환

SQL> select TO_DATE('May 3 2012','MONTH DD YYYY') from dual ;   TO_DATE(' --------- 03-MAY-12   SQL> select sysdate - to_date('20120101', 'yyyymmdd') from dual ;   SYSDATE-TO_DATE('20120101','YYYYMMDD') --------------------------------------                             123.577465   SQL> select to_date('2012/05/03', 'yyyy/mm/dd') from dual ;   TO_DATE(' --------- 03-MAY-12   SQL> select to_date('050312', 'MMDDYY') from dual ;   TO_DATE(' --------- 03-MAY-12   SQL> select to_date('20120503', 'yyyymmdd') from dual ;   TO_DATE(' --------- 03-MAY-12   SQL>

 

4. to_number function

 

Syntax : TO_NUMBER(문자값) / to_number( string1, [format_mask], [nls_language] )

-       string1 is the string that will be converted to a number.

-       format_mask is optional. This is the format that will be used to convert string1 to a number.

-       nls_language is optional. This is the nls language used to convert string1 to a number.

 

Example : 문자를 숫자로 변환

SQL> select TO_NUMBER('1234567') from dual ;   TO_NUMBER('1234567') --------------------              1234567   SQL> select to_number('1210.73', '9999.99') from dual ;    TO_NUMBER('1210.73','9999.99') ------------------------------                        1210.73   SQL> select to_number('546', '999') from dual ;    TO_NUMBER('546','999') ----------------------                    546   SQL> select to_number('23', '99') from dual ;    TO_NUMBER('23','99') --------------------                   23   SQL>

 

5. Syntax 의 형식에 대해서

 

형식 : 숫자를 문자로 변환시에 형식에 사용되는 요소

Parameter	Explanation
9	일반적인 숫자를 나타냄
0	앞의 빈자리를 0으로 채움
$	dollar를 표시함
L	지역 통화 단위
.	소숫점을 표시함
,	천단위를 표시함

 

  형식 : 날짜를 문자로 변환시에 형식에 사용되는 요소

Parameter	Explanation
SCC	세기를 표시 S는 기원전(BC)
YEAR	연도를 알파벳으로 spelling
YYYY	4자리 연도로 표시
YYY / YY / Y	끝의 3 / 2 / 1 자리 연도로 표시
Q	Quarter(분기) of year (1, 2, 3, 4; JAN-MAR = 1).
MONTH	월을 알파벳으로 spelling
MON	월의 알파벳 약어
MM	월을 2자리 숫자로 표시
RM	Roman numeral month (I-XII; JAN = I).
WW	Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year.
W	Week of month (1-5) where week 1 starts on the first day of the month and ends on the seventh.
DAY	일에 해당하는 요일, Name of day.
DY	일에 해당하는 요일의 약어
DDD	연도,월,일 중의 날짜를 숫자로 표시, Day of year (1-366).
DD	Day of month (1-31).
D	Day of week (1-7).
HH	시간을 표시, Hour of day (1-12).
HH12	시간을 표시, Hour of day (1-12).
HH24	시간을 표시, Hour of day (0-23).
MI	분을 표시, Minute (0-59).
SS	초를 표시, Second (0-59).
SSSSS	Seconds past midnight (0-86399).
FF	Fractional seconds. Use a value from 1 to 9 after FF to indicate the number of digits in the fractional seconds. For example, 'FF4'.
AM(A.M.),PM(P.M.)	오전인지 오후인지를 표시
AD or A.D	AD indicator
BC or B.C.	BC indicator
TZD	Daylight savings information. For example, 'PST'
TZH	Time zone hour.
TZM	Time zone minute.
TZR	Time zone region.

 

Reference : http://www.techonthenet.com/oracle/functions/index.php

End of dicument.