CAST

Syntax

CAST(expr AS type)

Purpose

CAST() explicitly converts the expression of a data type to another data type. That is, it converts the value specified by expr to a value of the data type specified by type.

Parameters

The parameters are described as follows:

• expr: a valid SQL expression.

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

• type: the data type supported in the target system. The supported data types are as follows:

  • CHAR[(N)] [CHARACTER SET charset_name]: generates a string of the CHAR data type. The data type of the result is CHAR(0) unless expr is null (length: 0). If N is specified, CHAR(N) will be forcibly converted to a string with no more than N arguments. No padding is performed for values with less than N characters. If N is not specified, OceanBase Database calculates the maximum length based on the expression. Without the CHARACTER SET charset_name clause, CHAR generates a string of the default character set.

  • DATE: generates a DATE value.

  • DATETIME [ (M) ]: generates a DATETIME value. M is optional and specifies the precision of seconds in the decimal place. The value range is [0,6].

  • DECIMAL [ (M [,D] ) ]: generates a DECIMAL value. M and D are optional and respectively specify the maximum number of digits (precision) and the number of decimal digits. The maximum value of M is 65, and that of D is 30. If D is omitted, the default value is 0. If M is omitted, the default value is 10.

  • SIGNED [INTEGER]: generates a signed BIGINT value.

  • JSON: generates a JSON value. For more information about the conversion rules between JSON and other data types, see Convert a JSON data type.

  • TIME [ (M) ]: generates a TIME value. If M is specified, it indicates the precision of seconds, that is, the number of decimal places for seconds.

  • UNSIGNED [INTEGER]: generates an unsigned BIGINT value.

Considerations

You can use the CAST function to convert data types in the following scenarios:

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

• The two expressions can be implicitly converted.

• The data type must be explicitly converted.

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, the maximum length of a VARCHAR value is 262,143 bytes, and that of a NUMBER value has a floating precision of 65 bits.

The CAST() function supports the operation on signed and unsigned 64-bit values. If you are using a numeric operator, such as +, and one of the operands is an unsigned integer, the result is unsigned. You can use SIGNED or UNSIGNED to explicitly specify the result. This way, the operation is specified as signed or unsigned 64-bit integers. If an operand is a floating-point value, the result is a floating-point value.

Examples

• Convert 0 to a DATE value.

  obclient> SELECT CAST(0 AS DATE);
  +-----------------+
  | CAST(0 AS DATE) |
  +-----------------+
  | 0000-00-00      |
  +-----------------+
  1 row in set
  

• Convert 123 to a TIME value.

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

• Convert 123 to a DATETIME value.

  obclient> SELECT CAST(123 AS DATETIME(4));
  +--------------------------+
  | CAST(123 AS DATETIME(4)) |
  +--------------------------+
  | 2000-01-23 00:00:00.0000 |
  +--------------------------+
  1 row in set
  

• Convert 123 to a DECIMAL value.

  obclient>  SELECT CAST(123 AS DECIMAL(3,2));
  +---------------------------+
  | CAST(123 AS DECIMAL(3,2)) |
  +---------------------------+
  |                      9.99 |
  +---------------------------+
  1 row in set
  

• Convert the string literal "123" to a JSON value.

  obclient> SELECT CAST("123" AS JSON);
  +---------------------+
  | CAST("123" AS JSON) |
  +---------------------+
  | 123                 |
  +---------------------+
  1 row in set
  

• Output the result of "1-2" separately as a signed value and an unsigned value.

  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
  

• Use CAST() for numeric calculation.

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

• Convert 123 to a CHAR value.

  obclient> SELECT CAST(123 AS CHAR(2));
  +----------------------+
  | CAST(123 AS CHAR(2)) |
  +----------------------+
  | 12                   |
  +----------------------+
  1 row in set
  

• Convert 1 to a CHAR value and specify a character set.

  obclient> SELECT CAST(1 AS CHAR CHARACTER SET utf8mb4);
  +---------------------------------------+
  | CAST(1 AS CHAR CHARACTER SET utf8mb4) |
  +---------------------------------------+
  | 1                                     |
  +---------------------------------------+
  1 row in set