Description of Oracle to_char usage

The following articles mainly describe the usage of Oracle to_char. Oracle to_char can be widely used in a short period of time, indicating that its practical application is dominant, the following is an introduction to the specific content of the article. I hope you will gain some benefits.

 
 

  
  
The following are 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 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 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".  
 
 

Use the Oracle function to_char to convert a number to a specified number of decimal places

Oracle to_char, 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 ---> '.123' */  
  
  
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 Oracle 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 ---> ' 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  
 
 

'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  
 
 

PLSQL Tips 1. Oracle's to_char () function is powerful, but pay attention to the following items when formatting numeric data. If it is a decimal point such as: 0.23, the data will be displayed after it passes through to_char. 23. If you want to display the original 0.23, you must use Oracle To_char (the value to be formatted, '0. 999 ') Keep three decimal places and display them, but pay attention to them here.

He rounded down the number of decimal places for you. Therefore, if you want to cut off decimals without rounding them, you should write a function to cut them down before normalization. To ensure that it is not rounded.

2. To_char (1.9999, 'fm90. 0999 ') when this function is normalized, 90.0999 indicates that 9 indicates that a number is displayed if no number is displayed, where there is 0, there will also be 0 to placeholder when there is no number. but this also has a major drawback, that is, if it is an integer, it will still display ". ", don't underestimate this point. Generally, this point is redundant if it is to be displayed on the page. it also caused us a lot of trouble. you need to write the function again to fix this small point.

3. A good solution is provided for Oracle inverted time of the date type. You can convert the date into a numeric type, and then Oracle To_char will display the type you need.

4. When using select into, be sure to use this method only when data is identified. if the query result is null, an error is returned. another case is that multiple pieces of data will also report an error. therefore, the cursor should be made as cheap as possible. this reduces the probability of errors.

5. Note that rownum does not support sorting, that is, if you want to use this to control the number of rows, you will find that it is not displayed in the order you specified ., this is a very difficult task. and if you use a statement like rownum = 2 to output the second line, it will not work.

6. The worst thing is that Oracle's null judgment is extraordinary. if you say a variable aa = null, it cannot be determined. although aa is indeed empty. it cannot be determined even in the selection conditions. I don't know why, so I had to use the nvl () function to judge. in addition to the condition, aa is null can be used to determine.

Add. when writing a stored procedure, note that the parameter name cannot be the same as the database field name. otherwise, Oracle regards this parameter name as the field name, even if you use the table alias to distinguish it. so pay attention to this when starting the parameter name.