oracle函数 re,oracle to_number函数格式化参数

,

(comma)

9,999

Returns a comma in the specified position. You can specify multiple

commas in a number format model.

Restrictions:

A

comma element cannot begin a number format model.

A

comma cannot appear to the right of a decimal character or period

in a number format model.

.

(period)

99.99

Returns a decimal point, which is a period (.) in the specified

position.

Restriction: You can

specify only one period in a number format model.

$

$9999

Returns value with a leading dollar sign.

0

0999

9990

Returns leading zeros.

Returns trailing zeros.

9

9999

Returns value with the specified number of digits with a leading

space if positive or with a leading minus if negative. Leading

zeros are blank, except for a zero value, which returns a zero for

the integer part of the fixed-point number.

B

B9999

Returns blanks for the integer part of a fixed-point number when

the integer part is zero (regardless of zeros in the format

model).

C

C999

Returns in the specified position the ISO currency symbol (the

current value of the NLS_ISO_CURRENCY parameter).

D

99D99

Returns in the specified position the decimal character, which is

the current value of the NLS_NUMERIC_CHARACTERparameter. The

default is a period (.).

Restriction: You can

specify only one decimal character in a number format model.

EEEE

9.9EEEE

Returns a value using in scientific notation.

G

9G999

Returns in the specified position the group separator (the current

value of the NLS_NUMERIC_CHARACTER parameter).

You can specify multiple group separators in a number format

model.

Restriction: A group

separator cannot appear to the right of a decimal character or

period in a number format model.

L

L999

Returns in the specified position the local currency symbol (the

current value of the NLS_CURRENCY parameter).

MI

9999MI

Returns negative value with a trailing minus sign (-).

Returns positive value with a trailing blank.

Restriction: The MI

format element can appear only in the last position of a number

format model.

PR

9999PR

Returns negative value in .

Returns positive value with a leading and trailing blank.

Restriction: The PR

format element can appear only in the last position of a number

format model.

RN

rn

RN

rn

Returns a value as Roman numerals in uppercase.

Returns a value as Roman numerals in lowercase.

Value can be an integer between 1 and 3999.

S

S9999

9999S

Returns negative value with a leading minus sign (-).

Returns positive value with a leading plus sign (+).

Returns negative value with a trailing minus sign (-).

Returns positive value with a trailing plus sign (+).

Restriction: The S

format element can appear only in the first or last position of a

number format model.

TM

TM

The text minimum number format model returns (in decimal output)

the smallest number of characters possible. This element is case

insensitive.

The default is TM9, which returns 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.

Restrictions:

You cannot precede this element with any other element.

You can follow this element only with one 9 or one E (or e), but

not with any combination of these. The following statement returns

an error:

SELECT TO_CHAR(1234, 'TM9e') FROM

DUAL;

U

U9999

Returns in the specified position the Euro (or other) dual currency

symbol, determined by the current value of theNLS_DUAL_CURRENCY parameter.

V

999V99

Returns a value multiplied by 10n (and if necessary, round

it up), where n is the

number of 9's after the V.

X

XXXX

xxxx

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.

Restrictions:

This element accepts only positive values or 0. Negative values

return an error.

You can precede this element only with 0 (which returns leading

zeroes) or FM. Any other elements return an error. If you specify

neither 0 nor FM with X, then the return always has one leading

blank.