Data type conversion |V2.2.77|OceanBase Database| docs|Distributed Database

Data type conversion

Last Updated：2023-08-18 09:26:34 Updated

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.

Data type	CHAR	VARCHAR2	NCHAR	NVARCHAR2	DATE	DATETIME / INTERVAL	NUMBER	BINARY_FLOAT	BINARY_DOUBLE	RAW	CLOB	BLOB
CHAR	-	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes
VARCHAR2	Yes	-	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes	-
NCHAR	Yes	Yes	-	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes	-
NVARCHAR2	Yes	Yes	Yes	-	Yes	Yes	Yes	Yes	Yes	Yes	Yes	-
DATE	Yes	Yes	Yes	Yes	-	-	-	-	-	-	-	-
DATETIME / INTERVAL	Yes	Yes	Yes	Yes	-	-	-	-	-	-	-	-
NUMBER	Yes	Yes	Yes	Yes	-	-	-	Yes	Yes	-	-	-
BINARY_FLOAT	Yes	Yes	Yes	Yes	-	-	Yes	-	Yes	-	-	-
BINARY_DOUBLE	Yes	Yes	Yes	Yes	-	-	Yes	Yes	-	-	-	-
RAW	Yes	Yes	Yes	Yes	-	Yes	-	-	-	-	Yes	-
CLOB	Yes	Yes	Yes	Yes	-	-	-	-	-	-	-	Yes
BLOB	-	-	-	-	-	-	-	-	-	Yes	-	-

^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

Data type	TO_CHAR	TO_VARCHAR2	TO_NCHAR	TO_NVARCHAR2
from CHAR	-	VARCHAR2	NCHAR	NVARCHAR2
from VARCHAR2	VARCHAR2	-	NVARCHAR2	NVARCHAR2
from NCHAR	NCHAR	NCHAR	-	NCHAR2
from NVARCHAR2	NVARCHAR2	NVARCHAR2	NVARCHAR2	-

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

Data type	To CHAR, VARCHAR2, NCHAR, and NVARCHAR2	To NUMBER	To Datetime/ Interval	To RAW	To CLOB and BLOB	To_BINARY_FLOAT	To_BINARY_DOUBLE
From CHAR, VARCHAR2, NCHAR, and NVARCHAR2	TO_CHAR(char . ) and TO_NCHAR(char . )	TO_NUMBER	TO_DATE, TO_TIMESTAMP, TO_TIMESTAMP_TZ, TO_YMINTERVAL, and TO_DSINTERVAL	HEXTORAW	TO_CLOB	TO_BINARY_FLOAT	TO_BINARY_DOUBLE
From NUMBER	TO_CHAR(number) and TO_NCHAR(number)	--	TO_DATE, NUMTOYM_INTERVAL, and NUMTOOLS_INTERVAL	--	--	TO_BINARY_FLOAT	TO_BINARY_DOUBLE
From Datetime/Interval	TO_CHAR(date) and TO_NCHAR(date)	--	--	--	--	--	--
From RAW	RAWTOHEX and RAWTONHEX	--	--	--	TO_BLOB	--	--
From CLOB and BLOB	TO_CHAR and TO_NCHAR	--	--	--	TO_CLOB	--	--
From BINARY_FLOAT	TO_CHAR(char . ) and TO_NCHAR(char . )	TO_NUMBER	--	--	--	TO_BINARY_FLOAT	TO_BINARY_DOUBLE
From BINARY_DOUBLE	TO_CHAR(char . ) and TO_NCHAR(char . )	TO_NUMBER	--	--	--	TO_BINARY_FLOAT	TO_BINARY_DOUBLE

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                    |
+-------------------------------+