SQL> select to_char(1123234,'c9999999') from dual 2 /TO_CHAR(112 ----------- USD1123234SQL> 转化成系统货币形式。
将数字转化为人民币(CNY)字符串
C:Specifies the location of the local currency symbol (such as $) in the return value. The NLS_CURRENCY parameter specifies the local currency symbol.9:Each 9 represents a significant digit to be returned. Leading zeros in a number are displayed as blanks.所以意思是最多为5位数字,以本地货币符号作为前缀
9 Each 9 represents a significant digit to be returned. Leading zeros in a number are displayed as blanks.
0 Each zero represents a significant digit to be returned. Leading zeros in a number are displayed as zeros.
$ Prefix: puts a dollar sign in front of a number.
B Prefix: returns a zero value as blanks, even if the 0 format element is used to show leading zeros.
MI Suffix: places a minus sign (-) after the number if it is negative. If the number is positive, a trailing space is placed after the number.
S Prefix: places a plus sign (+) in front of a positive number and a minus sign (-) in front of a negative number.
PR Suffix: places angle brackets (< and >) around a negative value. Positive values are given a leading and a trailing space.
D Specifies the location of the decimal point in the returned value. All format elements to the left of the D format the integer component of the value. All format elements to the right of the D format the fractional part of the value. The character used for the decimal point is determined by the database parameter NLS_NUMERIC_CHARACTERS.
G Specifies the location of the group separator (for example, a comma to separate thousands as in 6,754) in the returned value. The character used for the group separator is determined by the database parameter NLS_NUMERIC_CHARACTERS.
C Specifies the location of the ISO currency symbol in the returned value. The NLS_ISO_CURRENCY parameter specifies the ISO currency symbol.
L Specifies the location of the local currency symbol (such as $) in the return value. The NLS_CURRENCY parameter specifies the local currency symbol.
, (comma) Places a comma into the return value. This comma is used as a group separator (see the G format element).
. (period) Places a period into the return value. This period is used as a decimal point (see the D format element).
V Multiplies the number to the left of the V in the format model by 10 raised to the nth power, where n is the number of 9s found after the V in the format model.
EEEE Suffix: specifies that the value be returned in scientific notation.
RN or rn Specifies that the return value be converted to upper- or lowercase Roman numerals. The range of valid numbers for conversion to Roman numerals is between 1 and 3999. The value must be an integer. RN returns uppercase Roman numerals, while rn returns lowercase Roman numerals.
FM Prefix: removes any leading or trailing blanks from the return value.
TM Prefix: returns a number using the minimum number of characters. TM stands for "text minimum." Follow TM with one 9 if you want a regular, decimal notation (the default). Follow TM with one E if you want scientific notation.
U Places a Euro symbol at the specified location. The NLS_DUAL_CURRENCY parameter controls the character returned by this format element.
X Returns a number in hexadecimal value. You can precede this element with 0s to return leading zeros or with FM to trim leading and trailing blanks. X cannot be used in combination with any other format elements.
C:Specifies the location of the local currency symbol (such as $) in the return value. The NLS_CURRENCY parameter specifies the local currency symbol.9:Each 9 represents a significant digit to be returned. Leading zeros in a number are displayed as blanks.所以意思是最多为5位数字,以本地货币符号作为前缀正解
2 /TO_CHAR(112
-----------
USD1123234SQL> 转化成系统货币形式。
Each 9 represents a significant digit to be returned. Leading zeros in a number are displayed as blanks.
0
Each zero represents a significant digit to be returned. Leading zeros in a number are displayed as zeros.
$
Prefix: puts a dollar sign in front of a number.
B
Prefix: returns a zero value as blanks, even if the 0 format element is used to show leading zeros.
MI
Suffix: places a minus sign (-) after the number if it is negative. If the number is positive, a trailing space is placed after the number.
S
Prefix: places a plus sign (+) in front of a positive number and a minus sign (-) in front of a negative number.
PR
Suffix: places angle brackets (< and >) around a negative value. Positive values are given a leading and a trailing space.
D
Specifies the location of the decimal point in the returned value. All format elements to the left of the D format the integer component of the value. All format elements to the right of the D format the fractional part of the value. The character used for the decimal point is determined by the database parameter NLS_NUMERIC_CHARACTERS.
G
Specifies the location of the group separator (for example, a comma to separate thousands as in 6,754) in the returned value. The character used for the group separator is determined by the database parameter NLS_NUMERIC_CHARACTERS.
C
Specifies the location of the ISO currency symbol in the returned value. The NLS_ISO_CURRENCY parameter specifies the ISO currency symbol.
L
Specifies the location of the local currency symbol (such as $) in the return value. The NLS_CURRENCY parameter specifies the local currency symbol.
, (comma)
Places a comma into the return value. This comma is used as a group separator (see the G format element).
. (period)
Places a period into the return value. This period is used as a decimal point (see the D format element).
V
Multiplies the number to the left of the V in the format model by 10 raised to the nth power, where n is the number of 9s found after the V in the format model.
EEEE
Suffix: specifies that the value be returned in scientific notation.
RN or rn
Specifies that the return value be converted to upper- or lowercase Roman numerals. The range of valid numbers for conversion to Roman numerals is between 1 and 3999. The value must be an integer. RN returns uppercase Roman numerals, while rn returns lowercase Roman numerals.
FM
Prefix: removes any leading or trailing blanks from the return value.
TM
Prefix: returns a number using the minimum number of characters. TM stands for "text minimum." Follow TM with one 9 if you want a regular, decimal notation (the default). Follow TM with one E if you want scientific notation.
U
Places a Euro symbol at the specified location. The NLS_DUAL_CURRENCY parameter controls the character returned by this format element.
X
Returns a number in hexadecimal value. You can precede this element with 0s to return leading zeros or with FM to trim leading and trailing blanks. X cannot be used in combination with any other format elements.