[DB] [Oracle] how to format numbers in Oracle (specify the number of decimal places, each 3 digits plus a comma)

To_char is 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 ---> '. 100 '*/
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 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 elements in the numeric 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 ---> '123 '*/
Select To_char (0.123, '0. 100' ) From Dual
/* 100.12 ---> '######'*/
Select To_char (100.12, '0. 100' ) From Dual
/* 1.12 ---> '123 '*/
Select To_char (1.12, '0. 100' ) 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. 123' ) From Dual