The CAST function explicitly converts the expression of a source data type to another data type.
Syntax
CAST (expr AS type_name )
Parameters
| Parameter | Description |
|---|---|
| expr | The column name or the expression. |
| AS | Separates two parameters. The parameter before AS specifies the data to be processed. The parameter after AS specifies the data type to be converted to. |
| type_name | The data type is the built-in data type of ApsaraDB for OceanBase. For more information, see Built-in data types. |
Return type
The return type is the same as the data type of type_name.
The following table lists which data types can be converted to other built-in data types.
| from BINARY_FLOAT, BINARY_DOUBLE | from CHAR, VARCHAR2 | from NUMBER | ^1^ from DATETIME/INTERVAL | from RAW | from NCHAR, NVARCHAR2 | |
|---|---|---|---|---|---|---|
| to BINARY_FLOAT, BINARY_DOUBLE | yes | yes | yes | no | no | yes |
| to CHAR, VARCHAR2 | yes | yes | yes | yes | yes | no |
| to NUMBER | yes | yes | yes | no | no | yes |
| to DATETIME, INTERVAL | no | yes | no | yes | no | no |
| to RAW | yes | yes | yes | no | yes | no |
| to NCHAR, NVARCHAR2 | yes | no | yes | yes | yes | yes |
^1^To DATETIME/INTERVAL data types include DATE, TIMESTAMP, TIMESTAMP WITH TIMEZONE, INTERVAL DAY TO SECOND, and INTERVAL YEAR TO MONTH.
Examples
Execute the following statement:
SELECT CAST('123' AS INT),CAST(1 AS VARCHAR2(10)),CAST('22-OCT-1997' AS TIMESTAMP WITH LOCAL TIME ZONE)
AS RESULT FROM DUAL;
The following query result is returned:
+------------------+-----------------------+----------------------------+
| CAST('123'ASINT) | CAST(1ASVARCHAR2(10)) | RESULT |
+------------------+-----------------------+----------------------------+
| 123 | 1 | 1997-10-22 00:00:00.000000 |
+------------------+-----------------------+----------------------------+