Purpose
This function is used to convert one built-in data type to another.
Syntax
CAST ({ expr | MULTISET (subquery) } AS type_name )
Parameters
| Parameter | Description |
|---|---|
| expr | The name of a column or an expression. |
| MULTISET | If the result set of subquery can have multiple rows, you must specify the MULTISET keyword. The result of CAST(MULTISET (subquery) ) AS is a collection type, and the rows generated by the subquery are converted to corresponding collection elements. If you do not specify the MULTISET keyword, the subquery is considered a scalar subquery. |
| AS | A keyword used to separate the two parameters. The data before AS is the data to be processed, and the data after AS is the target data type. |
| type_name | The name of a built-in data type in OceanBase Database. |
Return type
The return type is the same as type_name.
The following table shows which data types can be converted to other built-in data types:
| from BINARY_FLOAT, BINARY_DOUBLE | from CHAR, VARCHAR2 | from NUMBER | 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 |
Note
The to DATETIME/INTERVAL data types include DATE, TIMESTAMP, TIMESTAMP WITH TIMEZONE, INTERVAL DAY TO SECOND, and INTERVAL YEAR TO MONTH.
Examples
The following examples convert the character 123 to an integer, the integer 1 to the VARCHAR2(10) type, and 22-OCT-2021 to the TIMESTAMP WITH LOCAL TIME ZONE type.
obclient> SELECT CAST('123' AS INT),CAST(1 AS VARCHAR2(10)),CAST('22-OCT-2021' AS TIMESTAMP WITH LOCAL TIME ZONE)
AS RESULT FROM DUAL;
+------------------+-----------------------+------------------------------+
| CAST('123'ASINT) | CAST(1ASVARCHAR2(10)) | RESULT |
+------------------+-----------------------+------------------------------+
| 123 | 1 | 22-OCT-21 12.00.00.000000 AM |
+------------------+-----------------------+------------------------------+
1 row in set
Note
When you convert data of the DATE type, this function is affected by the NLS_DATE_FORMAT parameter. We recommend that you execute the following statement to change the NLS_DATE_FORMAT format: ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
The following example shows how to use the MULTISET keyword in a table function to obtain the result of a collection type.
obclient> CREATE OR REPLACE TYPE tbl_int AS TABLE OF INT;
Query OK, 0 rows affected
obclient> CREATE TABLE t1(c1 INT, c2 INT);
Query OK, 0 rows affected
obclient> CREATE TABLE t2(c1 INT, c2 INT);
Query OK, 0 rows affected
obclient> INSERT INTO t1 VALUES (1,1);
Query OK, 1 row affected
obclient> INSERT INTO t2 VALUES (1,2);
Query OK, 1 row affected
obclient> INSERT INTO t2 VALUES (1,3);
Query OK, 1 row affected
obclient> SELECT * FROM t1,TABLE(CAST(MULTISET(SELECT t2.c2 FROM t2 WHERE t1.c1=t2.c1) AS tbl_int));
+------+------+--------------+
| C1 | C2 | COLUMN_VALUE |
+------+------+--------------+
| 1 | 1 | 2 |
| 1 | 1 | 3 |
+------+------+--------------+