Oracle to_char usage

Guidance:
The following are number examples forTo_charFunction.
To_char (1210.73, '192. 9') wocould return '192. 7'
To_char (1210.73, '20140901') wocould return '20160301'
To_char (1210.73, '$9,999.00') wocould return '$1,210.73'
To_char (21, '20140901') wocould return '20160901'
　　
The following is a list of valid parameters whenTo_charFunction is used to convert a date to a string. These parameters can be used in parallel combinations.
Parameter explanation
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 forTo_charFunction.
To_char (sysdate, 'yyyy/MM/dd'); wocould return '2017/09'
To_char (sysdate, 'month DD, yyyy'); wocould return 'july 09,200 3'
To_char (sysdate, 'fmmonth DD, yyyy'); wocould return 'july 9, 100'
To_char (sysdate, 'mon ddth, yyyy'); wocould return 'Jul 09th, 100'
To_char (sysdate, 'fmmon ddth, yyyy'); wocould return 'Jul 9th, 100'
To_char (sysdate, 'fmmon ddth, yyyy'); wocould return 'Jul 9th, 100'
You will notice that in some examples, the format_maskparameter 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'); wocould return 'july 9, 100'
To_char (sysdate, 'fmmon ddth, yyyy'); wocould return 'Jul 9th, 100'
To_char (sysdate, 'fmmon ddth, yyyy'); wocould return 'Jul 9th, 100'
The zeros have been suppressed so that the day component shows as "9" as opposed to "09 ".
　　Use the Oracle function to_char to convert a number to a specified number of decimal places
To_char is a function that converts a numeric or date type to a numeric type.
For example, the simplest application:
/* 1.0123 ---> '1. 0123 '*/
Select to_char (1.0123) from dual
/* 123 ---> '123 '*/
Select to_char (123) from dual
Next let's take a look at the following:
/* 0.123 ---> '. 100 '*/
Selec to_char (0.123) from dual
The above result '. 100' is not what we want in most cases. What we want is '0. 100 '.
Let's take a look at the specific usage of the to_char function:
　　To_char(N [, FMT [, 'nlsparam'])
　　
This function converts N of the number type into a value of the varchar2 type in the numeric format FMT. 'Nlsparams' specifies the characters returned by the element in numerical format, including:
. Decimal point character
. Group Separator
. Local coin symbol
. International coin symbol
The form of Yuan change is:
'Nls _ numeric_characters = "DG" nls_currency = "tcxt" nls_iso_currency = territory'
D indicates the decimal point, and G indicates the group separator.
Example: to_char (17145, 'l099g999', 'nls _ numeric_characters = ".," nls_currency = "NUD" ') = nud017, 145
Based on the above understanding, and then look at some FMT formats, we can use the following expression to get the value of '0. 123:
/* 0.123 ---> '123 '*/
Select to_char (0.123, '0. 999 ') from dual
/* 100.12 ---> '######'*/
Select to_char (100.12, '0. 999 ') from dual
/* 1.12 ---> '123 '*/
Select to_char (1.12, '0. 999 ') from dual
'123' is displayed, but there is a space in front.
The value of 100.12 is ######, and the value of '1. 12' is changed to '1. 120 '.
Let's re-determine a new requirement:
1. Remove Space
2. A maximum of four decimal places, with at least two digits retained.
1 ---> '1. 00'; 1.1 ---> '1. 00'; 1.12 --> '1. 12'; 1.1234 ---> '1. 100 ';
1.12345 ---> '1. 1235'
The final implementation is as follows:
/*
FM: Except Space
9999999.0099: the maximum number of digits to the left of the decimal point is 7 digits, and the number to the right of the decimal point is at least 2 digits. The maximum number is 4 digits, and the number is rounded to 5th digits.
*/
Select to_char (123.0233, 'fm9999999. 0099 ') from dual
　　To_char FunctionSpecial usage
To_char (sysdate, 'D') day of each week
To_char (sysdate, 'dd') day of each month
To_char (sysdate, 'ddd ') day of each year
To_char (sysdate, 'ww ') week of each year
To_char (sysdate, 'mm') month of each year
To_char (sysdate, 'q') quarter of each year
To_char (sysdate, 'yyyy') year
For example, if you want to find the day of the week
SQL> select to_char (to_date ('201312', 'yyyymmdd'), 'D') from dual;
Tag: Oracle

This article is transferred from
Http://www.cnblogs.com/wllyy189/archive/2008/06/04/1213433.html