CAST

Syntax

CAST(expr AS type)

Description

CAST() explicitly converts an expression of one data type to another data type. This function is used to convert the value of the expr field to the type data type.

Parameters

The parameters are described as follows:

• expr: An SQL expression.

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

• type: The data type of the target system. Supported types are as follows:

  • 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 you specify an optional length N, CHAR(N) forces the conversion of a string of characters that does not exceed N characters. If the value is shorter than N, it is not padded. If you do not specify the optional length N, seekdb calculates the maximum length based on the expression. If you do not specify the CHARACTER SET charset_name clause, the default character set is used.

  • DATE: Returns a DATE value.

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

  • DECIMAL [ (M [,D] ) ]: Returns 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. The maximum value for M is 65, and the maximum value for D is 30. If you omit D, it defaults to 0. If you omit M, it defaults to 10.

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

  • JSON: Generates a JSON value. For information on how to convert between the JSON data type and other data types, see Conversion rules for JSON data type.

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

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

  • DECIMAL(m,d): A fixed-point number type. m specifies the total number of digits, and d specifies 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 from a JSON array into a specific type of array.

Usage

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

• The data types of the two expressions are identical.

• The two expressions can be implicitly converted.

• You must explicitly convert the data types.

• If the value of the expr expression 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 may result in 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) may result in loss of precision.

• If the expr expression is of the VARCHAR type and not a numeric value, and you attempt to convert it to INT/INTEGER, SMALLINT, or TINYINT, the result will be 0.

• If the expr expression is of the JSON type and not a numeric value, and you attempt to convert it to INT/INTEGER, SMALLINT, or TINYINT, an error will be returned.

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

When users attempt an impossible conversion, seekdb will display an error message. If the length of the data type is not specified during conversion, seekdb will use its internal maximum length. For example, VARCHAR is 262,143 bytes, and NUMBER is 65 bits with floating point precision.

The CAST() function supports operations with both signed and unsigned 64-bit values. When using a numeric operator such as + 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 specify whether the operation should be with signed or unsigned 64-bit integers. If any of the operands is a floating-point value, the result is a floating-point value.

Examples

• Convert the numeric value 0 to the DATE type.

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

• Convert the numeric value 123 to the TIME type.

  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.

  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.

   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.

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

• Convert the result of "1-2" separately using unsigned and signed representations.

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

  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.

  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.

  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 the integer value 1 to the INT type.

  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 data [1,2,3] to an ARRAY data type consisting of INT type. The statement is as follows:

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