OceanBase Database supports decimal and hexadecimal numbers.
Decimal numbers
Decimal numbers can be divided into exact numbers and floating-point numbers. Exact numbers can be divided into integers and fixed-point numbers. You can add a decimal point (.) to a decimal number as a decimal separator. You can also add a minus sign (-) before a decimal number to indicate a negative value.
DECIMAL data type is a fixed-point data type that is used for precise calculations. The FLOAT and DOUBLE data types are floating-point data types that are used for approximate calculations.
An exact number can be an integer or a decimal. It can also contain both an integer and a fractional part. Examples: 1, .2, 3.4, -5, -6.78, and +9.10.
The approximate value of a number is expressed in the scientific notation format with a mantissa and an exponent. Examples: 1.2E3, 1.2E-3, -1.2E3, and -1.2E-3.
Two values that look the same may be different. For example, 2.34 is an exact fixed-point number, while 2.34E0 is the approximate value of a floating-point number.
Hexadecimal numbers
All hexadecimal numbers are integers. A hexadecimal number starts with X or 0x and contains letters from A to F. All letters in a hexadecimal number are case-insensitive.
Examples of valid hexadecimal numbers:
X'01AF'
X'01af'
x'01AF'
x'01af'
0x01AF
0x01af
Example of an invalid hexadecimal number:
X'0H' (H is not a hexadecimal number)
A hexadecimal number prefixed with X must contain an even number of digits. Otherwise, a syntax error occurs. To avoid syntax errors, pad X-prefixed values with a 0 when necessary. For example, you can pad the value X'FFF' with a zero to X'0FFF'.
If a hexadecimal number prefixed with 0x contains an odd number of digits, this number is treated as having a leading 0. For example, 0xaaF is treated as 0x0aaF.
By default, a hexadecimal number is a binary string, and each pair of the hexadecimal digits represents a character. Example:
obclient> SELECT 0x5461626c65, CHARSET(0x5461626c65);
+--------------+-----------------------+
| 0x5461626c65 | CHARSET(0x5461626c65) |
+--------------+-----------------------+
| Table | binary |
+--------------+-----------------------+
1 row in set
An empty hexadecimal value X' ' is evaluated as a zero-length binary string. X' ' is converted to a number and returns 0:
obclient> SELECT X''+0;
+-------+
| X''+0 |
+-------+
| 0 |
+-------+
1 row in set
To convert a string or number to a hexadecimal string, use the HEX() function:
obclient> SELECT HEX('dog');
+------------+
| HEX('dog') |
+------------+
| 646F67 |
+------------+
1 row in set
obclient> SELECT X'646F67';
+-----------+
| X'646F67' |
+-----------+
| dog |
+-----------+
1 row in set