Use of Oracle to_char Functions

The function of the Oracle to_char function is to convert the numeric or date type to the numeric type. The following describes the use of the Oracle to_char function in detail, hoping to help you.

The simplest application of Oracle to_char functions:

/* 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'])

The Oracle to_char function converts n of the NUMBER type into a value of the VARCHAR2 type in the numerical 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 ---> '123 '*/
Select TO_CHAR (0.123, '0. 999 ') FROM DUAL
/* 100.12 ---> '######'*/
Select TO_CHAR (100.12, '0. 999 ') FROM DUAL
/* 1.12 ---> '123 '*/
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
 

Implementation of oracle function return table

Two cases of Java calling Oracle Functions

Multiple table Connection Methods in Oracle

Example of using SQL recursive statements in oracle

How to uninstall an Oracle database in Windows