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 arguments. 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, 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
The following example converts 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';.
The following example uses the MULTISET keyword in a 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 |
+------+------+--------------+