Database

Syntax

Description

Oracle


Converts exp1, a value of CHAR, VARCHAR2, NCHAR or NVARCHAR2 data type containing a number in format specified by the optional format model exp2, to a value of NUMBER data type.
Return type: NUMBER.
The exp3 specifies characters that are returned by number format elements:
� Decimal character
� Group separator
� Local currency symbol
� International currency symbol.
A number format model (exp2) is composed of one or more number format elements as listed in following:

Element

Example

Description

, (comma)

9,999

Returns a comma in the specified position. Multiple commas maybe specified.
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: Only one period maybe specified.

$

$9999

Returns value with a leading dollar sign.

0

0999

Returns leading zeros.

9990

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 fixedpoint number.

B

B9999

Returns blanks for the integer part of a fixedpoint number when the integer part is zero (regardless of "0"s in the format model).

C

C999

Returns in the specified position the ISO currency symbol (the current value of the NLS_ISO_CURRENCY parameter in exp3).

EEEE

9.9EEEE

Returns a value using in scientific notation.

D

99D99

Returns in the specified position the decimal character, which is the current value of the NLS_NUMERIC_CHARACTER parameter in exp3. The default is a period (.).
Restriction: Only one decimal character maybe specify.

FM

FM90.9

Returns a value with no leading or trailing blanks.



G

9G999

Returns in the specified position the group separator (the current value of the NLS_NUMERIC_CHARACTER parameter in exp3). Multiple group separators maybe specify in a number format model.
Restriction: A group separator cannot appear to the right of a decimal character or period.

L

L999

Returns in the specified position the local currency symbol (the current value of the NLS_CURRENCY parameter in exp3).

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 a negative value in <angle brackets>. Returns a 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

Returns a value as Roman numerals in uppercase. A value can be an integer between 1 and 3999.



rn

rn

Returns a value as Roman numerals in lowercase. Value can be an integer between 1 and 3999.

S

S9999

Returns a negative value with a leading minus sign (). Returns a positive value with a leading plus sign (+).
Restriction: The S format element can appear only in the first or last position of a number format model.

9999S

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

TM

TM

"Text minimum". Returns (in decimal output) the smallest number of possible characters. This element is caseinsensitive.
The default is TM9, which returns the number in fixed notation unless the output exceeds 64 characters. If output exceeds 64 characters, then Oracle automatically returns the number in scientific notation.
Restrictions:
� This element cannot precede with any other element.
� This element can follow only with 9 or E (only one) or e (only one).



U

U9999

Returns in the specified position the "Euro" (or other) dual currency symbol (the current value of the NLS_DUAL_CURRENCY parameter in exp3).

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

Returns the hexadecimal value of the specified number of digits. If the specified number is not an integer, then Oracle rounds it to an integer.
Restrictions:
� This element accepts only positive values or 0. Negative values return an error.
� This element can precede 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 1 leading blank.

MySQL


Converts a value of exp1 to a value of UNSIGNED or SIGNED INTEGER data type.
If it uses numerical operations (like +) and one of the operands is unsigned as integer, the result will be unsigned. This can be overrided by using the SIGNED and UNSIGNED cast operators to cast the operation to a signed or unsigned 64bit integer, respectively.
