Data type conversion

Generally, an expression cannot contain values of different data types. However, to enable expressions to be calculated, ApsaraDB for OceanBase allows you to implicitly and explicitly convert values from one data type to another.

Implicitly convert data types

If a conversion makes sense, ApsaraDB for OceanBase automatically converts values from one data type to another. Rules of implicitly converting data types are:

• For INSERT and UPDATE operations, ApsaraDB for OceanBase converts variable values into the column type.

• For SELECT FROM operations, ApsaraDB for OceanBase converts the column data type into the destination variable type.

• When character values are compared with numeric values, ApsaraDB for OceanBase converts the character values into numeric values.

• When numeric values are processed, ApsaraDB for OceanBase adjusts the precision and decimal places. The generated numeric data type is different from the numeric data type that is found in the base table.

• Conversions between character values or numeric values and floating-point numbers can be inexact. This is because character types and numeric types use decimal precision to represent numeric values but floating-point numbers use binary precision.

• Assume that a value is converted from CLOB to a character type, such as VARCHAR2, or from BLOB to RAW. If the size of data to be converted is larger than the size of the destination data type, the database returns an error.

• When timestamp values are converted into DATE values, the fractional second portion of the timestamp values is truncated and rounded.

• The result of converting BINARY_FLOAT to BINARY_DOUBLE is exact.

• If the number of digits of precision for BINARY_DOUBLE exceeds the number of digits supported by BINARY_FLOAT, the result of converting BINARY_DOUBLE to BINARY_FLOAT is inexact.

• When character values are compared with DATE values, ApsaraDB for OceanBase converts character data into DATE values.

• When a value is assigned, ApsaraDB for OceanBase converts the value on the right side of the equal sign into the data type of the object to which the value is assigned on the left.

• ApsaraDB for OceanBase converts non-character types into character types or national character types during join operations.

Matrix of implicit data type conversions

The following table lists all implicit conversions between data types. You do not need to consider the directions or contexts of conversions. Hyphens (-) indicate that the conversion is not supported.

^1^ You cannot directly convert from RAW to INTERVAL. However, you can use UTL_RAW.CAST_TO_VARCHAR2([RAW]) to convert from RAW to VARCHAR2. Then, convert the generated VARCHAR2 value to INTERVAL.

Directions of implicit conversions between different character types

Examples of implicit data type conversions

Execute the following statement:

SELECT 5 * 10 + 'james' FROM DUAL;

The statement fails to be executed and you receive the following error message:

invalid number

This is because ApsaraDB for OceanBase implicitly converts 'james' into the numeric type, but fails to convert it.

In this example, the string '2' is implicitly converted from the CHAR data type into the value 2 of the numeric data type. The calculation result is 52 .

Execute the following statement:

SELECT 5 * 10 + '2' FROM  DUAL;

The following query result is returned:

+---------------------+
|     5 * 10 + '2'    |     
+---------------------+
|         52          |  
+---------------------+

Explicitly convert data types

You can use SQL conversion functions to convert data types. SQL functions explicitly convert one data type to another.

Matrix of explicit data type conversions

Examples of explicit data type conversions

You can use the TO_CHAR function to explicitly convert the current time into the desired format for output.

Execute the following statement:

SELECT TO_CHAR(SYSDATE, 'YYYY_MM_DD') FROM DUAL;

The following query result is returned:

+-------------------------------+
| TO_CHAR(SYSDATE,'YYYY_MM_DD') |
+-------------------------------+
| 2020_02_27                    |
+-------------------------------+