The following are number examples for the to_char function.
to_char (1210.73, \'9999 .9 \ ') would return \'1210 .7 \'
to_char (1210.73, \'9999 .99 \ ') would return \'1210 .73 \'
to_char (1210.73, \ '$ 9999.00 \') would return \ '$ 1210.73 \'
to_char (21, \'000099 \ ') would return \'000021 \'
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). [Page]
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 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 \".
Oracle function to_char into the designated number of decimal-digit Usage
to_char, function features that will be value-or-date into a character.
For example, the simplest application:
/ * 1.0123 ---> \'1 .0123 \ '* /
Select TO_CHAR (1.0123) FROM DUAL
/ * 123 ---> \'123 \ '* /
Select TO_CHAR (123) FROM DUAL
Then look at the following:
/ * 0.123 ---> \ '.123 \' * /
SELEC TO_CHAR (0.123) FROM DUAL
The above results of the \ '.123 \' in most cases are not the result we want, we want to be the \'0 .123 \ '.
We look at the specific function to_char Usage:
TO_CHAR (n [, fmt [, \ 'nlsparam \']])
NUMBER function of the types of n by numerical format fmt into VARCHAR2 types of value. \ 'nlsparams \' format specified by the value of the element of the return of characters, including:
. Decimal point characters
. Group separator
. Local coin symbol
. International money symbols
Arguments in the form of:
\ 'NLS_NUMERIC_CHARACTERS = \ "dg \" NLS_CURRENCY = \ "tcxt \" NLS_ISO_CURRENCY = territory \'
D one decimal point for the character, g for the group separator.
Cases: TO_CHAR (17145, \ 'L099G999 \', \ 'NLS_NUMERIC_CHARACTERS = \ "., \" NLS_CURRENCY = \ "NUD \" \') = NUD017, 145
Through the above understanding, and then to see some of the fmt format, we can use the following expressions have been \'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 [Page]
/ * 1.12 ---> \ '1.120 \' * /
Select TO_CHAR (1.12, \'0 .999 \ ') FROM DUAL
\ '.123 \' Is out, but more in front of a space.
The value of 100.12 and is ######, \'1 .12 \ 'value into the \'1 .120 \'.
We have to re-establish a new demand:
1 to space
2, up to 4 decimal point, to retain at least 2.
1 ---> \'1 .00 \ '; 1.1 ---> \'1 .00 \'; 1.12 -> \'1 .12 \ '; 1.1234 ---> \'1 .1234 \';
--- 1.12345> \'1 .1235 \ '
The ultimate realization of the following:
/ *
FM: In addition to space
9999999.0099: to allow the left of the decimal point is the largest number 7, the right of the decimal point at least 2, up to 4 and No. 5 in the conduct of rounding
* /
Select TO_CHAR (123.0233, \ 'FM9999999.0099 \') FROM DUAL
--------------- JoeCom 2006-08-08
posted on 2007-01-04 18:12 Sammy read (1829) comments (3) editors cited collection network pick
Comments
# Re: oracle to_char use 2007-01-04 18:49 Sammy
PLSQL experience in a small, Oracle's to_char () function is very powerful features but with its numerical data format should please note the following. If it is small, such as: 0.23 This to_char after data showed that a .23 will be, if you show it to the original 0.23 if you use To_char (to the numerical format,'0 .999 ') to retain the three decimal And show that, but here it is necessary to pay attention to the. He was the interception of a small number when you are rounded up. If the request is so truncated, then rounding decimals rather than on their own should go on to write a cut-off function after standardized. To ensure that it does not rounded.
Second, To_char (1.9999, 'FM90.0999') that function at 90.0999 standardized meaning where there is a 9, if there is no figure on the show, if not the number shows that there is 0 place in the absence of a time when the number will be 0 To a placeholder. But this will also have a major disadvantage is that if the round is when it will show "." Not to the point of Xiao Qiao, general page to show if this is the point of excess. We also Caused no small trouble. Would also like to write their own function to get the little point. Directly or call the system function: rtrim (to_char (1, \ 'FM90.999 \'), \ '. \')
Third, with regard to the date-based Oracle would provide a good approach can make value-date. To_char then be able to show that you need the type.
Fourth, select into the use of time must note that this method you must be sure to confirm the data when it was found to be using. If the results of inquiries for space-time would result in error. There is a check data Also a number of error. Cursor should be cheap to do as much as possible. Will have to reduce the chance of error.
Fifth, there is little attention sort rownum does not support, that is, you want to use this to control the number of rows, then he will find that you did not specify the sort shown. This is a very difficult thing to do. And if you rownum 2 = such a statement to output in the second line, then it is feasible. VI, the most disgusting thing is null for Oracle to determine the abnormal to the extreme. If you say a variable aa = null it is not to judge. Despite the aa is empty. Even in the choice of conditions, is not to judge. do not know why, with no choice but to nvl () to determine the function of this. In addition to the conditions can be used to determine aa is null.
Add six. Is written in the store when the process should pay attention to the parameters of the database and can not field the same name. Otherwise, Oracle will be the parameter name as a field of 000, even if you used the alias table to distinguish between them. Therefore, since the parameters When people have to pay attention to this point. |
