CAST

Syntax

CAST(expr AS type)

Description

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

Parameters

The parameters are described as follows:

• expr: an expression of any valid SQL data type.

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

• type: the data type of 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, the default character set is used.

  • DATE: generates a DATE value.

  • DATETIME [ (M) ]: generates a DATETIME value. M is an optional parameter that specifies the precision of seconds in the fractional part, with a range of [0,6].

  • DECIMAL [ (M [,D] ) ]: generates a DECIMAL value. M and D are optional parameters that specify the maximum number of digits (precision) and the number of digits after the decimal point (scale), respectively. M can be up to 65, and D can be up to 30. If D is omitted, it defaults to 0; if M is omitted, it defaults to 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 the fractional part.

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

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

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

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

  • SMALLINT: a 16-bit signed integer.

  • TINYINT: an 8-bit signed integer.

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

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

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

• Converting a fixed-point number (DECIMAL(m,d)) or a floating-point number (DOUBLE or FLOAT) to an integer (INT/INTEGER, SMALLINT, or TINYINT) results 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 returns 0.

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

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

If an impossible conversion is attempted, OceanBase Database displays an error message. If the length of the 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 is 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 is 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 "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" using 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 a numerical operation 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
  

  • Convert the string 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 CHAR(10)) |
    +--------------------+---------------------+
    |                123 | 1                   |
    +--------------------+---------------------+
    1 row in set (0.035 sec)
    

• Convert the JSON array [1,2,3] to an ARRAY of INT type.

  obclient> SELECT CAST('[1,2,3]' AS ARRAY<INT>);
  +-------------------------------+
  | CAST('[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>> data type.

  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