Oracle Common functions To_char usage (go from Blog Park-Little Grass blog)

The following is number examples for the to_char function.

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, ' $9,999.00 ')	Would return ' $1,210.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 the year.
Iyy IY I	Last 3, 2, or 1 digit (s) of the ISO year.
Iyyy	4-digit year based in 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 in 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 the 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 is 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'll notice that in some examples, the format_mask parameter begins with "FM". This means, zeros and blanks are suppressed. This can is 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 has been suppressed so, the day component shows as "9" as opposed to "09".

Oracle function TO_CHAR Conversion number specifies the number of decimal places to use

To_char, function function, is to convert a numeric or date type to a character 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

Then look at the following:

/*0.123---> '. 123 ' */
Selec to_char (0.123) from DUAL

The above result '. 123 ' is not the result we want in most cases, we want to be ' 0.123 '.

Let's take a look at the specific use of the TO_CHAR function:

To_char (n [, Fmt [, ' Nlsparam ']])

This function converts N of type number to the value of the VARCHAR2 type by numeric format FMT.' Nlsparams ' specifies the characters returned by elements in numeric format, including:

． Decimal decimal character

． Group Separator

． Local coin symbol

． International coin Symbol

The form of the variable is:

' Nls_numeric_characters= ' dg "nls_currency=" TCXT "Nls_iso_currency=territory"

where D is a decimal character and G is a group separator.

Example: To_char (17145,' l099g999 ',' nls_numeric_characters= '), "nls_currency=" NUD "') =nud017,145

Through the above understanding, and then see some of the FMT format, we can use the following expression to get the value of ' 0.123 ':

/*0.123---> ' 0.123 ' */
Select to_char (0.123,' 0.999 ') from DUAL
/*100.12---> ' ###### ' * *
Select to_char (100.12,' 0.999 ') from DUAL
/*1.12---> ' 1.120 ' */
Select to_char (1.12,' 0.999 ') from DUAL

' 0.123 ' came out, but there was a space ahead.

The value for 100.12 is ######, and the value of ' 1.12 ' becomes ' 1.120 '.

We re-define a new requirement:

1, go to space

2, the decimal point up to 4 bits, the minimum reserved 2 bits.

1---> ' 1.00 '; 1.1---> ' 1.00 ';1.12--> ' 1.12 '; 1.1234---> ' 1.1234 ';

1.12345---> ' 1.1235 '

The final implementation is as follows:

/*
FM: Except Spaces
9999999.0099: Allow 7 digits to the left of the decimal point, minimum 2 digits to the right of the decimal point, up to 4 bits, and rounding in 5th place
*/
Select to_char (123.0233,' FM9999999.0099 ') from DUAL

Plsql Small experience One, Oracle's To_char () function is powerful but you should be aware of the following when formatting numeric data with it. If it is a decimal number such as: 0.23 such data after To_char will be changed to. 23, if you want it to show the original 0.23, you have to use TO_CHAR (to format the value, ' 0.999 ') to keep three decimal places, and display but here is to pay attention. He rounded up the decimal when he intercepted it for you. So if you want to cut off a decimal and not round it, you should write a function to cut down and then normalize. To ensure that it does not round.

II, To_char (1.9999, ' FM90.0999 ') This function normalized when the meaning of 90.0999 is that there are 9 of places if there are numbers to show that if there is no number is not shown, 0 of the place in the absence of numbers will also have a zero. But there is also a big drawback, that is, if it is an integer it will still show ".", do not underestimate this point, In general, this point is superfluous if the page is to be displayed. It also caused us a great deal of trouble. Write the function again to get this little bit done.

Third, for the date-type Oracle pour time provides a good method, you can make the date into a numerical type. and then To_. Char will show you the type you need.

Iv. When using SELECT INTO, be sure to note that This way you must be sure that there will be no data to use until it is detected. If the query result is empty, an error will result. There is also a case where the data found is multiple and will be error-free. So it should be done as cheaply as possible. It reduces the chance of errors.

v. And note that a little bit of rownum does not support sorting, that is, if you want to use this to control the number of rows, you will find that he did not appear in the sort order you specified. This is a very difficult thing to do. And if you use a rownum=2 statement to output the second line, it won't work. The most disgusting point is that Oracle's judgment of NULL is perverted to the extreme. If you say that a variable aa=null it is not to be judged. Although AA is indeed empty. Even in the selection conditions are not judged. I don't know why, I had to use NVL () This function to judge. Outside of the condition can be judged by the AA is null. Add a little to the

Six. It is important to note that the parameter name cannot be the same as the database field name when you write the stored procedure. Otherwise Oracle will treat this parameter name as a field name, even if you distinguish it from the alias of the table. So be sure to pay attention to this when starting the parameter name.

Oracle Common Functions To_char usage (go from Blog Park-Little Grass blog)