CAST

Syntax

CAST(expr AS type)

Purpose

The CAST() function converts an expression of one data type to another data type, converting the value of the expr field to the type data type.

Parameters

The parameters are described as follows:

• expr: specifies any valid SQL expression.

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

• type: specifies the data type provided by the target system. Supported types include:

  • CHAR[(N)] [CHARACTER SET charset_name]

    Generates a string with the CHAR data type. If the expression expr is empty (length 0), the result type is CHAR(0). If an optional length N is specified, CHAR(N) will convert to a string with a length not exceeding N characters, without padding for values shorter than N characters. If no optional length N is specified, OceanBase Database will calculate the maximum length based on the expression. If no CHARACTER SET charset_name clause is specified, CHAR will generate a string with the default character set.

  • DATE: generates a DATE value.

  • DATETIME [ (M) ]: generates a DATETIME value. The M value is optional and specifies the precision of seconds in the fractional part, ranging from 0 to 6.

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

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

  • JSON: generates a JSON value. For conversion rules between JSON and other data types, see JSON data type conversion.

  • TIME [ (M) ]: generates a TIME value. If an optional M value is specified, it indicates the precision of seconds in the fractional part.

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

Considerations

When using the CAST function for data type conversion, the following scenarios are supported:

• The data types of the two expressions are identical.

• The two expressions can be implicitly converted.

• The data type must be explicitly converted.

If an impossible conversion is attempted, OceanBase Database will display an error message. If the length of the target data type is not specified during conversion, the maximum length supported by the OceanBase Database system will be used. For example, VARCHAR supports up to 262,143 bytes, and NUMBER supports up to 65 bits of floating-point precision.

The CAST() function supports operations with 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 will be unsigned. You can explicitly declare the result using SIGNED and UNSIGNED to specify the operation as a signed or unsigned 64-bit integer, respectively. If either operand is a floating-point value, the result will be a floating-point value.

Examples

• Convert the number 0 to the DATE type.

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

• Convert the number 123 to the TIME type.

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

• Convert the number 123 to the DATETIME type.

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

• Convert the number 123 to the DECIMAL type.

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

• Convert the string text "123" to the JSON type.

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

• Convert the result of "1-2" to both unsigned and signed representations.

  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
  

• Perform numerical operations using CAST().

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

• Convert the number 123 to the CHAR type.

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

• Convert the number 1 to the CHAR type and specify the character set.

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