CAST

Syntax

CAST(expr AS type)

Purpose

You can call the CAST() function to explicitly convert the expression of a data type to another data type, that is, convert the value specified by expr to a value of the data type specified by type.

Parameters

The parameters are described as follows:

• expr: specifies a valid SQL expression.

• AS: separates two arguments. 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. Supported data types:

  • 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. Value range: [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.

Usage notes

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 value length is not specified for the target data type, the maximum length of the data type 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.

CAST() supports the calculation of 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 SIGNED and UNSIGNED to explicitly declare the result and assign the operation to 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