Purpose
CAST() converts one built-in data type to another built-in data type.
Syntax
CAST ({ expr | MULTISET (subquery) } AS type_name )
Parameters
| Parameter | Description |
|---|---|
| expr | The column name or expression. |
| MULTISET | If the result set of subquery can contain multiple rows, you must specify the MULTISET keyword. The result of CAST(MULTISET (subquery) ) AS is a collection, and the rows resulting from the subquery are converted to collection elements. If the MULTISET keyword is not specified, the subquery is executed as a scalar subquery. |
| AS | Used to separate two parameters. The data to be converted is located before AS, and the data type to which the data is to be converted is located after AS. |
| type_name | The name of a built-in data type of OceanBase Database. |
Return type
The return type is the same as what you specified for the type_name parameter.
The following table describes the conversion relationships between the 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 and 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
DATETIME/INTERVAL data types include DATE, TIMESTAMP, TIMESTAMP WITH TIMEZONE, INTERVAL DAY TO SECOND, and INTERVAL YEAR TO MONTH.
Examples
Convert the character string 123 to an integer, the integer 1 to the VARCHAR2(10) data type, and 22-OCT-2021 to the TIMESTAMP WITH LOCAL TIME ZONE data 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
This function is affected by the NLS_DATE_FORMAT parameter when you use it to convert a value of the DATE data type. You can execute the following statement to convert the NLS_DATE_FORMAT format first: ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';.
Use the MULTISET keyword in the Table function to obtain a result of the 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 |
+------+------+--------------+