CAST

Syntax

CAST(expr AS type)

Purpose

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 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 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 the string to use no more than N characters. Values shorter than N characters will not be padded. If no optional length N is specified, OceanBase Database will calculate the maximum length based on the expression. If the CHARACTER SET charset_name clause is not 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 decimal part, 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. 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 more information about the 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 specifies the precision of seconds in the decimal 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 numeric 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.

Considerations

When using the CAST function for data type conversion, the following cases 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 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 be returned during conversion.

If an impossible conversion is attempted, OceanBase Database will display an error message. If the length of the data type is not specified during conversion, the maximum length supported by the OceanBase Database system will be used. For example, VARCHAR can be up to 262,143 bytes, and NUMBER can be 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 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 numeric 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 numeric 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 numeric 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 numeric 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.

  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 CAST().

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

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

• Convert the JSON array [1,2,3] to an ARRAY of INT type. The statement is as follows:

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 (0.035 sec