FLOAT data type

The FLOAT data type is a subtype of the NUMBER data type that is specified with precision. This data type occupies 4 bytes to 40 bytes of storage space. The precision is calculated based on the number of significant binary digits which ranges from 1 to 126. The number of decimal places is not user-defined. The FLOAT data type specifies the variable-length non-exact numeric data.

Syntax

FLOAT [(p)]

Parameters

Note

• Binary-to-decimal precision conversion: Binary precision = int(Decimal precision x 0.30103)

• Decimal-to-binary precision conversion: Decimal precision = int(Binary precision x 3.32193)

Examples

• Example 1 : Assume that you set the binary precision to 2 by using FLOAT, convert the binary precision to the decimal precision by using the following function: int(2 x 0.30103) = 0.6, and round down the result to an integer. The decimal precision of FLOAT(2) is 0.

FLOAT(2)

• Example 2 : Assume that you create a table named test , and insert data into the table. The col1 column is of the NUMBER data type, and the col2 column is of the FLOAT data type. NUMBER(5,2) represents a fixed-point value with decimal precision. The result is limited to five significant digits, and is returned with two decimal places. FLOAT(5) represents the binary precision of 5. This value is converted to the decimal precision by using the following function: int(5 x 0.30103) = 1.50515. After the result is rounded down to an integer, the decimal precision is 1. For example, 123.45 is expressed as 1.2345 x 10^2^ in scientific notation. If one decimal place is required, the value of 1.2345 is rounded to 1.2. The following result is finally displayed: 1.2 x 10^2^ = 120. Execute the following statement:

CREATE TABLE test (col1 NUMBER(5,2), col2 FLOAT(5));
INSERT INTO test VALUES (1.23, 1.23);
INSERT INTO test VALUES (7.89, 7.89);
INSERT INTO test VALUES (12.79, 12.79);
INSERT INTO test VALUES (123.45, 123.45);

Execute the following statement to query the test table:

SELECT * FROM test;

The following result is returned:

+-----------+----------+
| col1          |   col2   |
+-----------+----------+
|    1.23   |   1.2    |
+-----------+----------+
|    7.89   |   7.9    |
+-----------+----------+
|    12.79  |   13     |
+-----------+----------+
|    123.45 |   120    |
+-----------+----------+

Note

When you convert the ANSI FLOAT data, you can use the FLOAT data type that is used by ApsaraDB for OceanBase. However, we recommend that you use BINARY_FLOAT and BINARY_DOUBLE for floating-point numbers in ApsaraDB for OceanBase.