All Products
Search
Document Center

Conversion functions

Last Updated: Jun 18, 2021

CAST

Declaration

CAST(expr AS type)

Description

This function explicitly converts an expression of one data type to another.

It converts the expr field value into the type data type.

Parameters

  • expr: specifies a valid SQL expression.

  • AS: separates two parameters. The value before AS specifies the data to be processed, and the value after AS specifies the target data type.

  • type: specifies the data type provided by the target system. Valid values:

    • DATE

    • DATETIME

    • DECIMAL

    • SIGNED [INTEGER]

    • TIME

    • UNSIGNED [INTEGER]

You can use the CAST function to convert data types when at least one of the following conditions is met.

  • The data types of the two expressions are the same.

  • The two expressions can be implicitly converted.

  • The data type must be converted explicitly.

If you attempt to make an unacceptable conversion, OceanBase Database returns an error message.

If the data type length is not specified, the maximum length in the OceanBase Database system is used. For example, VARCHAR is 262,143 bytes, and NUMBER has a floating precision of 65 bits.

This function supports the operation on signed and unsigned 64-bit values. If you are using a numeric operator (for example, +) and one of the operands is an unsigned integer, the result is unsigned. You can use the SIGNED and UNSIGNED cast operators to overwrite it. In this way, the operation is assigned to signed or unsigned 64-bit integers.

If an operand is a floating-point value, the result is a floating-point value.

Example

obclient> SELECT CAST(123 AS TIME);
+-------------------+
| CAST(123 AS TIME) |
+-------------------+
| 00:01:23          |
+-------------------+
1 row in set (0.01 sec)

obclient> select cast(1-2 as unsigned), cast(cast(1-2 as unsigned) as signed);
+-----------------------+---------------------------------------+
| cast(1-2 as unsigned) | cast(cast(1-2 as unsigned) as signed) |
+-----------------------+---------------------------------------+
|  18446744073709551615 |                                    -1 |
+-----------------------+---------------------------------------+
1 row in set (0.00 sec)

obclient> SELECT CAST(1 AS UNSIGNED) - 2.0;
+---------------------------+
| CAST(1 AS UNSIGNED) - 2.0 |
+---------------------------+
| -1.0 |
+---------------------------+
1 row in set (0.00 sec)

obclient> select cast(0 as date);
+-----------------+
| cast(0 as date) |
+-----------------+
| 0000-00-00      |
+-----------------+
1 row in set (0.00 sec)