Use of the To_number () function in Oracle __c language

similar to MySQL

You can use cast, but to fill in the appropriate type, sample:

Select CAST (one as unsigned int)//* Integral type * *

Select CAST (10,2)/* Floating-point type * *

(1) converts a string of char or VARCHAR2 type to a numeric value of type number .

It is important to note that the converted string must conform to the numeric type format if the converted

the string does not conform to the numeric format, and Oracle throws the error hint;

(2) To_number and To_char are exactly two opposite functions;

To_number (varchar2 or char, ' format ')

[SQL] view plain copy Select To_number (' 000012134 ') from dual; Select To_number (' 88877 ') from dual;

(2) If the number is within the scope of the format, it is correct, otherwise it is wrong;

[SQL] view plain copy Select To_number (' $12345.678 ', ' $999999.99 ') from dual; Select To_number (' $12345.678 ', ' $999999.999 ') from dual;

(3) can be used to achieve the conversion of the system, 16 to 10 into the system:

[SQL] view plain copy Select To_number (' 19f ', ' xxx ') from dual; Select To_number (' F ', ' xx ') from dual;
1 Introduction to Usage

The To_number function () is one of the commonly used type conversion functions in Oracle, mainly converting a string into a numeric format, which is the opposite of the function of the To_char ().

The To_number function is formatted as follows:

To_number (varchar2 or char, ' format model ')

1 1

There are also many predefined fixed formats in the To_number function:

Format Value	meaning
9	Represents a number
0	Force 0 Display
$	Show dollar sign
L	Force display of a local currency symbol
.	Show a decimal point
,	Display a thousand separator symbol

21 Some examples

Sql> Select To_number (' rmb234234.4350′, ' l999999.0000′ ') from dual;
    To_number (' rmb234234.4350′, ' l999999.0000′ ')
    ——————————————
    234234.435

1 2 3 4 1 2 3 4

Sql> Select To_number (' $123,233,455,623.3400′, ' $999,999,999,999.0000′ ') from dual;
    To_number (' $123,233,455,623.3400′, ' $999,999,999,999.0000′ ')
    ———————————————————-
    1.2323E+11

1 2 3 4 1 2 3 4 3 Usage Traps

Sometimes you will find that using the To_number () function and the syntax is correct, but Oracle reported "invalid number" error, and you again and again to check carefully and make sure that the statement is correct after a big surprise, think To_number () A function may not know what to use. This is probably a problem with the data you're querying, not SQL. When using the To_number () function, make sure that the converted field is convertible to a number, such as the string "20151008" that can be converted to the number 20151008, but the string "2015-10-08" is not available. If your field contains the string "2015-10-08", and you use the To_number () function directly, you will report "Invalid number" error. 4 Escape from the trap

How to escape the trap. 1 Front End Check

Try to make the necessary checks as the user enters, making sure that the input value is the format we need. 2 Background check

Perform the necessary checks in the background code, filter to the wrong values, and pass to the front desk for reasonable hints. 3 SQL Checksum