CAST

Syntax

CAST(expr AS type)

Purpose

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

Parameters

The parameters are described as follows:

• expr: any valid SQL expression.

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

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

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

    Generates a string of 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) forces the conversion to use no more than N characters; values shorter than N characters are not padded. If no optional length N is specified, OceanBase Database calculates the maximum length based on the expression. If the CHARACTER SET charset_name clause is not specified, CHAR generates 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 decimal places, ranging from [0,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 of M is 65, and the maximum value of 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 provided, it specifies the precision of seconds in decimal places.

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

  • DECIMAL(m,d): a fixed-point number type, where m represents the total number of digits and d represents the number of decimal places.

  • FLOAT: a single-precision floating-point number, suitable for approximate numeric values.

  • BIGINT: explicitly converts the value of the expression expr to the BIGINT type.

    Note

    This type was introduced in OceanBase Database V4.6.0.

  • INT or INTEGER: a 32-bit signed integer.

  • SMALLINT: a 16-bit signed integer.

  • TINYINT: an 8-bit signed integer.

  • ARRAY: converts elements in a JSON array to an array of a specific type. Supports the ARRAY<element_type> format for declaring array element types and allows nested arrays, such as ARRAY<ARRAY<INT>>, for representing multidimensional array structures.

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 the numeric value of the expression expr exceeds the numeric range of the target data type, the type conversion will return null.

• Converting a high-precision data type to a low-precision data type will result in a loss of precision.

• Converting a fixed-point number (DECIMAL(m,d)) or a floating-point number (DOUBLE, FLOAT) to an integer (INT/INTEGER, SMALLINT, or TINYINT) will result in a loss of precision.

• If the expression expr is of the VARCHAR type and not a numeric value, converting it to INT/INTEGER, SMALLINT, or TINYINT will return 0.

• If the expression expr is of the JSON type and not a numeric value, converting it to INT/INTEGER, SMALLINT, or TINYINT will result in an error.

• If the VARCHAR or JSON data does not conform to the ARRAY format, an error will occur during conversion.

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 is 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 (e.g., +) and one of the operands is an unsigned integer, the result will be unsigned. You can explicitly declare the result using SIGNED or UNSIGNED to specify whether the operation should be treated as a signed or unsigned 64-bit integer. If either operand is a floating-point value, the result will be a floating-point value.

Examples

• Convert the value 0 to the DATE type.

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

• Convert the value 123 to the TIME type.

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

• Convert the value 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 value 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 "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 an unsigned and a signed representation, respectively.

  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 numeric operations using the CAST() function.

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

• Convert the value 123 to the CHAR type.

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

• Convert the value 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
  

• Convert the character value 123 to an integer and convert the integer 1 to the INT type.

  obclient> SELECT CAST('123' AS INT),CAST(1 AS CHAR(10));
  +--------------------+---------------------+
  | CAST('123' AS INT) | CAST(1 AS INT) |
  +--------------------+---------------------+
  |                123 | 1                   |
  +--------------------+---------------------+
  1 row in set (0.035 sec
  

• Convert the JSON array [1,2,3] to an ARRAY of INT type, as shown in the following statement:

  obclient> SELECT CAST( JSON '[1,2,3]' AS ARRAY<INT>);
  +-------------------------------------+
  | CAST( JSON '[1,2,3]' AS ARRAY<INT> |
  +-------------------------------------+
  | [1,2,3]                             |
  +-------------------------------------+
  1 row in set
  

• Convert the nested JSON array [[1,2],[3,4]] to a nested ARRAY<ARRAY<INT>> type, as shown in the following statement:

  obclient> SELECT CAST('[[1,2],[3,4]]' AS ARRAY<ARRAY<INT>>);
  +--------------------------------------------+
  | CAST('[[1,2],[3,4]]' AS ARRAY<ARRAY<INT>>) |
  +--------------------------------------------+
  | [[1,2],[3,4]]                              |
  +--------------------------------------------+
  1 row in set
  

• Convert the floating-point value 123.456 to the BIGINT type.

  obclient> SELECT CAST(123.456 AS BIGINT);
  

  The return result is as follows:

  +-------------------------+
  | CAST(123.456 AS BIGINT) |
  +-------------------------+
  |                     123 |
  +-------------------------+
  1 row in set