About format models in oracle it is specified inside post, http://arjudba.blogspot.com/2009/08/format-models-in-oracle.html
Number format models is the format by which you can recognize oracle about the input data of number type.
Number format models are used in the following functions.
1)TO_CHAR function : TO_CHAR function is used to translate a value of NUMBER, BINARY_FLOAT, or BINARY_DOUBLE datatype to VARCHAR2 datatype.
2)TO_NUMBER function : TO_NUMBER function is used to translate a value of CHAR or VARCHAR2 datatype to NUMBER datatype.
3)TO_BINARY_FLOAT and TO_BINARY_DOUBLE function : TO_BINARY_FLOAT and TO_BINARY_DOUBLE functions are used to translate CHAR and VARCHAR2 expressions to BINARY_FLOAT or BINARY_DOUBLE values.
Consider of using Number format Models
significant digits
Below is an example.
SQL> select to_char(343543.98,'$999.99') from dual;
TO_CHAR(
--------
########
Here number 343543.98 has total 6 digits on the left of the decimal but in the formal model it is used only 3 digits. So instead of returning real digit # sign is returned.
If we used correct or more number of 9's than required then it would display right characters.
SQL> select to_char(343543.98,'$9999999.99') from dual;
TO_CHAR(3435
------------
$343543.98
and the input is either infinity or NaN (not a number), then Oracle always returns
the pound signs to replace the value.
Number Format Elements
Number format model is composed of one or more number format elements. List of number format elements are shown below with examples.
1)Comma(,): Return a comma in the specified position. Comma can't place at the beginning position or at the right to the decimal point.
Example:
SQL> select to_char(98687,'9,999,9') from dual;
TO_CHAR(
--------
9,868,7
2)Period(.): Return a decimal point in the specified position. Can have at best only one decimal point.
Example:
SQL> select to_char(98687,'99999.99') from dual;
TO_CHAR(9
---------
98687.00
If the number of digits to the left of the decimal point in the format model is less than the number of digits to the left of the decimal point exist in the real number then # is returned.
SQL> select to_char(98687,'9999.999') from dual;
TO_CHAR(9
---------
#########
If there is multiple period(.) then following error returned.
SQL> select to_char(98687,'99999.9.9') from dual;
select to_char(98687,'99999.9.9') from dual
*
ERROR at line 1:
ORA-01481: invalid number format model
3)Dollar sign($):
If used within format models then character with leading dollar sign is returned. Can be used any position within format models but not in multiple position.
Example:
SQL> select to_char(93,'9$9') from dual;
TO_C
----
$93
SQL> select to_char(93,'99$') from dual;
TO_C
----
$93
Using $ into multiple position fails with ORA-01481.
SQL> select to_char(93,'$$99') from dual;
select to_char(93,'$$99') from dual
*
ERROR at line 1:
ORA-01481: invalid number format model
4)Zero(0):
Usage of zero(s)(0) inside format models return leading zeros(0) if inside format model they are used to the left of the decimal point. Multiple usage will return multiple leading zeros. If inside format model zero(s) are used to the right in the decimal point then digits to the right of the real number is filled first and then zero is placed based on the deducting from number of zero(s) inside from model minus number of digits to the right of the real number.
Examples will clear you more.
SQL> select to_char(93,'099') from dual;
TO_C
----
093
SQL> select to_char(93,'99.0') from dual;
TO_CH
-----
93.0
SQL> select to_char(93,'099000') from dual;
TO_CHAR
-------
000093
SQL> select to_char(93.98,'099.000') from dual;
TO_CHAR(
--------
093.980
SQL> select to_char(93,'099.000') from dual;
TO_CHAR(
--------
093.000
5)9:
In the above example you find in all number format model 9 is used. 9 returns value with the specified number of digits with a leading space if positive or with a leading minus if negative. For leading 0 in the number, 9 in the format model prints blank.
Note that leading 0 becomes NULL unless the number is zero.
SQL> select to_char(09,'9') from dual;
TO
--
9
SQL> select to_char(09,'99') from dual;
TO_
---
9
SQL> select to_char(0,'99') from dual;
TO_
---
0
6)B:
B inside format model returns blanks for the integer part of a fixed-point number when the integer part is zero.
Following is an example.
SQL> select to_char(0,'99') from dual;
TO_
---
0
SQL> select to_char(0,'B99') from dual;
TO_
---
7)C:
C returns the ISO currency symbol in the specified position. The value is determined from the NLS_ISO_CURRENCY parameter.
SQL> select to_char(9,'C99') from dual;
TO_CHAR(9,
----------
USD9
SQL> select to_char(9,'9C9') from dual;
TO_CHAR(9,
----------
9USD0
SQL> select to_char(9,'99C') from dual;
TO_CHAR(9,
----------
9USD
8)D:
Returns a numeric character in the specified position. The character is specified by the parameter NLS_NUMERIC_CHARACTERS. The default is a period (.).
Example:
SQL> select to_char(99,'999D99') from dual;
TO_CHAR
-------
99.00
9)EEEE:
EEEE returns a value using in scientific notation.
Example:
SQL> select to_char(9939,'9EEEE') from dual;
TO_CHAR
-------
1E+04
SQL> select to_char(9939,'9.EEEE') from dual;
TO_CHAR(
--------
1.E+04
SQL> select to_char(9939,'9.9EEEE') from dual;
TO_CHAR(9
---------
9.9E+03
SQL> select to_char(9939,'9.9999EEEE') from dual;
TO_CHAR(9939
------------
9.9390E+03
10)G:
G returns the group separator in the specified position. The current value is specified by the NLS_NUMERIC_CHARACTER parameter. You can specify multiple group
separators in a number format model.
Example:
SQL> select property_value from database_properties where upper(property_name)='NLS_NUMERIC_CHARACTERS';
PROPERTY_V
----------
.,
SQL> select to_char(9939,'99G99') from dual;
TO_CHA
------
99,39
SQL> select to_char(9939,'99G9G9') from dual;
TO_CHAR
-------
99,3,9
Note that a group separator cannot appear to the right of a decimal character or
period in a number format model.
11)L:
L returns the local currency symbol in the specified position. The current value is specified by the parameter NLS_CURRENCY.
Example:
SQL> select to_char(9939,'L9999') from dual;
TO_CHAR(9939,'L
---------------
$9939
SQL> select to_char(9939,'9999L') from dual;
TO_CHAR(9939,'9
---------------
9939$
12)MI:
For negative value MI returns a trailing minus sign (-).
For positive value MI returns a trailing blank.
Note that MI format element can appear only in the last position of a number format model.
Example:
SQL> select to_char(34,'9999MI') from dual;
TO_CH
-----
34
SQL> select to_char(-34,'9999MI') from dual;
TO_CH
-----
34-
13)PR:
For negative value PR returns value in <angle brackets>.
For positive value PR returns a a leading and trailing blank.
Note that PR format element can appear only in the last position of a number format model.
Example:
SQL> select to_char(-34,'9999PR') from dual;
TO_CHA
------
<34>
SQL> select to_char(34,'9999PR') from dual;
TO_CHA
------
34
14)RN/rn:
RN returns a value as Roman numerals in uppercase.
rn returns a value as Roman numerals in lowercase.
Note that value would be in the integer range between 1 and 3999. If it is outside range it can't display in Roman numerals.
Example:
SQL> select to_char(34,'RN') from dual;
TO_CHAR(34,'RN'
---------------
XXXIV
SQL> select to_char(44434,'RN') from dual;
TO_CHAR(44434,'
---------------
###############
SQL> select to_char(434,'rn') from dual;
TO_CHAR(434,'RN
---------------
cdxxxiv
15)S:
If we use S as leading position in the format model like S999 then it
returns negative value with a leading minus sign (-).
returns positive value with a leading plus sign (+).
And if we use S as trailing position in the format model like 999S then it
returns negative value with a trailing minus sign (-).
returns positive value with a trailing plus sign (+).
Note that the S format element can appear only in the first or last position of a
number format model.
Example:
SQL> select to_char(-434,'999S') from dual;
TO_C
----
434-
SQL> select to_char(434,'S999') from dual;
TO_C
----
+434
16)TM:
TM The text minimum number format model returns (in decimal output) the smallest
number of characters possible.
The default is TM9, which means it will return the number in fixed notation unless the output exceeds 64 characters.
If the output exceeds 64 characters, then Oracle Database automatically returns the number in scientific notation.
Note that, you must use TM leading position in the format model and you can follow this element only with one 9 or one E (or e).
Example:
SQL> select to_char(343000,'TME') from dual;
TO_CHAR(343000,'TME')
-------------------------------------------------
3.43E+05
17)U:
U returns the Euro (or other) dual currency symbol in the specified position. The return value is based on the NLS_DUAL_CURRENCY parameter.
Example:
SQL> select to_char(786,'U999') from dual;
TO_CHAR(786,'U
--------------
$786
18)V:
V returns a value multiplied by power(10,n) (and if necessary, round it up), where n is the number of 9’s after the V.
Example:
SQL> select to_char(786,'999V9') from dual;
TO_CH
-----
7860
SQL> select to_char(786,'999V99') from dual;
TO_CHA
------
78600
19)X:
X returns the hexadecimal value of the specified number of digits. If the specified
number is not an integer, then Oracle Database rounds it to an integer.
Note that, while using X format models,
- You can't use X format models on negative number.
- The format model X only can precede with 0 or FM.
- If you dont specify 0 or FM with X, then the return value always has one leading blank.
Example:
SQL> select to_char(17,'XXXXX') from dual;
TO_CHA
------
11
SQL> select to_char(17,'0XXXXX') from dual;
TO_CHAR
-------
000011
SQL> select to_char(17,'FMXXXXX') from dual;
TO_CHA
------
11
Related Documents
No comments:
Post a Comment