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.

+ Recent posts