ORACLE ORCLE 문자열을 숫자로, 숫자를 문자로, TO_CHAR(), TO_NUMBER()

2013.12.27 19:44

Oracle/PLSQL: TO_CHAR Function

The Oracle/PLSQL TO_CHAR function converts a number or date to a string.

Oracle TO_CHAR Syntax

The syntax for the Oracle/PLSQL TO_CHAR function is:

TO_CHAR( value, [ format_mask ], [ nls_language ] )

Parameters or Arguments

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.

Applies To

The TO_CHAR function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

TO_CHAR Function Examples - with Numbers

Let's look at some Oracle TO_CHAR function examples and explore how you would use the TO_CHAR function in Oracle/PLSQL.

For example:

The following are number examples for the TO_CHAR function.

경축! 아무것도 안하여 에스천사게임즈가 새로운 모습으로 재오픈 하였습니다.
어린이용이며, 설치가 필요없는 브라우저 게임입니다.
https://s1004games.com

TO_CHAR(1210.73, '9999.9')	would return '1210.7'
TO_CHAR(1210.73, '9,999.99')	would return '1,210.73'
TO_CHAR(1210.73, ',999.00')	would return '16985,210.73'
TO_CHAR(21, '000099')	would return '000021'

TO_CHAR Funcation Examples - with Dates

The following is a list of valid parameters when the TO_CHAR function is used to convert a date to a string. These parameters can be used in many combinations.

Parameter	Explanation
YEAR	Year, spelled out
YYYY	4-digit year
YYY YY Y	Last 3, 2, or 1 digit(s) of year.
IYY IY I	Last 3, 2, or 1 digit(s) of ISO year.
IYYY	4-digit year based on the ISO standard
Q	Quarter of year (1, 2, 3, 4; JAN-MAR = 1).
MM	Month (01-12; JAN = 01).
MON	Abbreviated name of month.
MONTH	Name of month, padded with blanks to length of 9 characters.
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.
IW	Week of year (1-52 or 1-53) based on the ISO standard.
D	Day of week (1-7).
DAY	Name of day.
DD	Day of month (1-31).
DDD	Day of year (1-366).
DY	Abbreviated name of day.
J	Julian day; the number of days since January 1, 4712 BC.
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.

The following are date examples for the TO_CHAR function.

TO_CHAR(sysdate, 'yyyy/mm/dd');	would return '2003/07/09'
TO_CHAR(sysdate, 'Month DD, YYYY');	would return 'July 09, 2003'
TO_CHAR(sysdate, 'FMMonth DD, YYYY');	would return 'July 9, 2003'
TO_CHAR(sysdate, 'MON DDth, YYYY');	would return 'JUL 09TH, 2003'
TO_CHAR(sysdate, 'FMMON DDth, YYYY');	would return 'JUL 9TH, 2003'
TO_CHAR(sysdate, 'FMMon ddth, YYYY');	would return 'Jul 9th, 2003'

You will notice that in some TO_CHAR function examples, the format_mask parameter begins with "FM". This means that zeros and blanks are suppressed. This can be seen in the examples below.

TO_CHAR(sysdate, 'FMMonth DD, YYYY');	would return 'July 9, 2003'
TO_CHAR(sysdate, 'FMMON DDth, YYYY');	would return 'JUL 9TH, 2003'
TO_CHAR(sysdate, 'FMMon ddth, YYYY');	would return 'Jul 9th, 2003'

The zeros have been suppressed so that the day component shows as "9" as opposed to "09".

Frequently Asked Questions (TO_CHAR Function)

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";

본 웹사이트는 광고를 포함하고 있습니다.
광고 클릭에서 발생하는 수익금은 모두 웹사이트 서버의 유지 및 관리, 그리고 기술 콘텐츠 향상을 위해 쓰여집니다.

ORCLE 문자열을 숫자로, 숫자를 문자로, TO_CHAR(), TO_NUMBER(), 이 게시물을

번호	제목	글쓴이	날짜	조회 수
공지	오라클 기본 샘플 데이터베이스	졸리운_곰	2014.01.02	86191
공지	[SQL컨셉] 서적 "SQL컨셉"의 샘플 데이타 베이스 SAMPLE DATABASE of ORACLE	가을의 곰을...	2013.02.10	78669
공지	[G_SQL] Sample Database	가을의 곰을...	2012.05.20	95405
5	[PostgreSQL] 열을 행으로 전환 쿼리	졸리운_곰	2023.01.29	1192
4	[postgreSQL] PostgreSQL 계층형 쿼리 구현 방법	졸리운_곰	2023.01.29	1896
3	[postgreSQL] ORACLE쿼리에서 postgreSQL쿼리 변환	졸리운_곰	2023.01.29	1511
2	[postgreSQL] [PostgreSQL] stored function(stored procedures) 사용하기	졸리운_곰	2023.01.23	1727
1	[postgreSQL] [Docker] Docker에 PostgreSQL 설치하기	졸리운_곰	2023.01.21	2035

첫 페이지 1 끝 페이지

쓰기

태그