Many data conversion processing operations encounter the need to convert 0.007007040000 to 0.7%, and we use Oracle's SQL function To_char to implement this transformation.
This function is used to convert a date or Number data type to a string that can be displayed, in the form of To_char (Number_type, Format_mask).
The format ' 999.99 ', 9 indicates the value of a specified number of digits, and if the value is 0, it is ignored, and if the specified number of digits has no value, it is represented by a space.
Format ' 0990.990 ', 0 indicates the value of a specified number of digits, or 0 if the value is 0, or 0 if no value is displayed.
Format ' FM990.90 ', FM indicates that the display of the number of strings to be displayed without a value of the space to clean up, the role and LTrim similar.
Copy Code code as follows:
Sql> Select To_char (12304.560, ' 999.99 ') from dual;
To_char (12304.560, ' 999.99 ')
---------------------------
#######
Sql> Select To_char (104.560, ' 999.99 ') from dual;
To_char (104.560, ' 999.99 ')
-------------------------
104.56
Sql> Select To_char (104.560, ' 99999.99 ') from dual;
To_char (104.560, ' 99999.99 ')
---------------------------
104.56
Sql> Select To_char (104.560, ' 99999.990 ') from dual;
To_char (104.560, ' 99999.990 ')
----------------------------
104.560
Sql> Select To_char (104.560, ' 0099.990 ') from dual;
To_char (104.560, ' 0099.990 ')
---------------------------
0104.560
Copy Code code as follows:
Sql>
Sql> Select To_char (Round (0.007007040000, 4) *, ' FM99999999990.90 ') | | '% ' as AA,
2 Length (To_char (Round (0.007007040000, 4) *, ' FM99999999990.90 ') | | '% ') as BB
3 from dual;
AA BB
---------------- ----------
0.7% 5
Sql>
Sql> Select To_char (Round (0.007007040000, 4) * 100, ' 99999999990.90 ') | | '% ' as AA,
2 Length (To_char (Round (0.007007040000, 4) * 100, ' 99999999990.90 ') | | '% ') as BB
3 from dual;
AA BB
---------------- ----------
0.7% 16
This is a common operational feature that TO_CHAR converts numbers to strings, as well as other formats, see Format templates for To_char (numeric).
Copy Code code as follows:
Template description
9 A value with a specified number of digits
0 Leading Zero values
. (period) decimal point
, (comma) grouping (thousand) separator
Negative in PR angle brackets
Negative value of S with minus sign (using localized)
L currency symbol (using localization)
D decimal (use localized)
G grouping separators (using localization)
MI at the indicated position of the minus sign (if number < 0)
PL at the indicated position of the plus sign (if number > 0)
The positive/negative sign of the SG at the specified position
RN roman Numerals (input between 1 and 3999)
th or th are converted into ordinal numbers
V move n Bit (decimal) (see note)
EEEE scientific count. Now not supported.
It also converts time-formatted data into strings, but the format is more complex.
