Basic Oracle Tutorial: Convert the number into a string using the to_char Function

Many data conversion and processing operations may encounter the need to convert 0.007007040000 to 0.70%. We can use the Oracle SQL function to_char to achieve this conversion.

This function is used to convert the DATE or NUMBER data type to a printable string in the format of to_char (number_type, format_mask ).

In the format of '1970. 99', 9 indicates the value of a specified number of digits. If the value is 0, it is ignored and not displayed. If the specified number of digits does not have a value, it is expressed by a space.

The format is 0990. 990. 0 indicates the value of a specified number of digits. If the value is 0, it is displayed as 0. If there is no value, it is also displayed as 0.

The format is 'fm990. 90'. FM indicates clearing the spaces that are displayed because the number of positions in the displayed string has no value. This is similar to ltrim.

SQL> select to_char (12304.560, '192. 99') from dual;
 
TO_CHAR (12304.560, '192. 99 ')
---------------------------
#######
 
SQL> select to_char (104.560, '192. 99') from dual;
 
TO_CHAR (104.560, '192. 99 ')
-------------------------
104.56
 
SQL> select to_char (104.560, '192. 99') from dual;
 
TO_CHAR (104.560, '192. 99 ')
---------------------------
104.56
 
SQL> select to_char (104.560, '192. 99999 ') from dual;
 
TO_CHAR (104.560, '192. 100 ')
----------------------------
104.560
 
SQL> select to_char (104.560, '192. 0099 ') from dual;
 
TO_CHAR (104.560, '192. 100 ')
---------------------------
0104.560

SQL code

SQL>
SQL> select to_char (round (0.007007040000, 4) * 100, 'fm99999999990. 90') | '%' as aa,
2 length (to_char (round (0.007007040000, 4) * 100, 'fm99999999990. 90') | '%') as bb
3 from dual;
 
AA BB
--------------------------
0.70% 5
 
SQL>
SQL> select to_char (round (0.007007040000, 4) * 100, '192. 90') | '%' as aa,
2 length (to_char (round (0.007007040000, 4) * 100, '192. 90') | '%') as bb
3 from dual;
 
AA BB
--------------------------
0.70% 16

This is a common function for to_char to convert numbers into strings. For more information, see the to_char (numeric) format template.

Template code

Template description
9. A value with a specified number of digits
0 leading zero value
. (Period) decimal point
, (Comma) Grouping (thousands) Separator
Negative value in PR angle brackets
Negative value of S with negative signs (use localization)

L currency symbols (use localization)
D decimal point (use localization)
G grouping separator (use localization)
Negative Number of MI at the specified position (if the number is <0)
The positive number of PL at the specified position (if the number is greater than 0)
Positive/negative signs of SG at the specified position
RN roman numerals (input between 1 and 3999)
TH or th to ordinal number
V moves n digits (decimal places) (see annotations)
EEEE Scientific records. Not Supported now.

It can also convert time format data into strings, but the format is more complex.