AnalyticDB for MySQL allows you to use CAST functions to convert values from one data type to another. This topic describes how to use CAST functions.

Syntax

cast(expr AS type)
The following section describes the fields used in the syntax:
  • expr: indicates the source data, such as a string with the value of 'China'.
  • type: indicates the destination data type, such as VARBINARY.

CAST AS BOOLEAN

cast (expr AS boolean)
  • Description: converts the value specified by expr to the BOOLEAN type.
  • Source data types:
    • DECIMAL(M,D), FLOAT, INT/INTEGER, SMALLINT, or TINYINT
    • BIGINT
    • DOUBLE
    • VARCHAR
    • JSON
  • Example:
    Execute the following statement to convert the value 1 from the INT type to the BOOLEAN type:
    SELECT cast('1' AS boolean);
    The following result is returned:
    +----------------------+
    | cast('1' AS boolean) |
    +----------------------+
    |                    1 |
    +----------------------+
  • Precautions:
    • You may encounter the following scenarios when you convert a value from the VARCHAR or JSON type to the BOOLEAN type:
      • If the input value is true or 1, a value of 1 is returned.
      • If the input value is false or 0, a value of 0 is returned.
      • If the input value is of a different data type, an error is returned.
      For example, execute the following statement to convert the value a from the VARCHAR type to the BOOLEAN type:
      SELECT cast('a' AS boolean);
      The following error message is returned:
      ERROR 1815 (HY000): [30013, 2021091710344119216818804803453912763] : Cannot cast 'a' to BOOLEAN
    • You may encounter the following scenarios when you convert a value from the DOUBLE type to the BOOLEAN type:
      • If the input value is a number other than 0.0, a value of 1 is returned.
      • If the input value is 0.0, a value of 0 is returned.
      For example, execute the following statement to convert the value 4.3 from the DOUBLE type to BOOLEAN type:
      SELECT cast(4.3 AS boolean);
      The following result is returned:
      +----------------------+
      | cast(4.3 AS boolean) |
      +----------------------+
      |                    1 |
      +----------------------+
    • You may encounter the following scenarios when you convert a value from the DECIMAL(M,D), FLOAT, INT/INTEGER, SMALLINT, TINYINT, or BIGINT type to the BOOLEAN type:
      • If the input value is a number other than 0, a value of 1 is returned.
      • If the input value is 0, a value of 0 is returned.
      For example, execute the following statement to convert the value 5 from the INT type to the BOOLEAN type:
      SELECT cast(5 AS boolean);
      The following result is returned:
      +--------------------+
      | cast(5 AS boolean) |
      +--------------------+
      |                  1 |
      +--------------------+

CAST AS DECIMAL(m,d)|FLOAT|INT/INTEGER|SMALLINT|TINYINT

cast (expr AS decimal(m,d)|float|int/integer|smallint|tinyint)
  • Description: converts the value specified by expr to the DECIMAL(M,D), FLOAT, INT/INTEGER, SMALLINT, or TINYINT type.
  • Source data types:
    • DECIMAL(M,D), FLOAT, INT/INTEGER, SMALLINT, or TINYINT
    • BIGINT
    • DOUBLE
    • VARCHAR
    • JSON
  • Example:
    Execute the following statement to convert the value 2001012 from the BIGINT type to the FLOAT type:
    SELECT cast( 2001012 AS float);
    The following result is returned:
    +-------------------------+
    | cast( 2001012 AS float) |
    +-------------------------+
    |               2001012.0 |
    +-------------------------+
  • Precautions:
    • If a value of the source data type is not within the allowed value range of the destination data type, an error is returned during the conversion.
      For example, execute the following statement to convert the value 99999999 from the BIGINT type to the SMALLINT type:
      SELECT cast(99999999 as smallint);
      The following error message is returned because 99999999 is not within the allowed value range of the SMALLINT type:
      ERROR 1815 (HY000): [31002, 2021091417365619216818804803453260208] : Out of range for smallint: 99999999
    • In the following scenarios, data is rounded after the conversion:
      • Convert data from the DOUBLE type to the FLOAT type.
        Execute the following statement to convert the value 1.23456789 from the DOUBLE type to the FLOAT type:
        SELECT cast(1.23456789 AS float);
        The following result is returned:
        +---------------------------+
        | cast(1.23456789 AS float) |
        +---------------------------+
        |                 1.2345679 |
        +---------------------------+
      • Convert data between the DECIMAL(M,D), FLOAT, INT/INTEGER, SMALLINT, and TINYINT types.
        For example, execute the following statement to convert the value 1.1342 from the FLOAT type to the INT type:
        SELECT cast(1.1342 AS int);
        The following result is returned:
        +---------------------+
        | cast(1.1342 AS int) |
        +---------------------+
        |                   1 |
        +---------------------+
    • If the input VARCHAR value is not a number, an error is returned when the input value is converted to the DECIMAL(M,D) or FLOAT type.
      For example, execute the following statement to convert the value China from the VARCHAR type to the DECIMAL(M,D) type:
      SELECT cast( 'China' AS decimal(5,2));
      The following error message is returned:
      ERROR 1815 (HY000): [30013, 2021091715273019216818804803453961857] : Cannot cast VARCHAR 'China' to DECIMAL(5, 2). Value is not a number.
    • If the input VARCHAR value is not a number, a value of 0 is returned after the input value is converted to the INT/INTEGER, SMALLINT, or TINYINT type.
      For example, execute the following statement to convert the value China from the VARCHAR type to the SMALLINT type:
      SELECT cast( 'China' AS smallint);
      The following result is returned:
      +----------------------------+
      | cast( 'China' AS smallint) |
      +----------------------------+
      |                          0 |
      +----------------------------+
    • If the input JSON value is not a number, an error is returned when the input value is converted to the DECIMAL(M,D), FLOAT, INT/INTEGER, SMALLINT or TINYINT type.
      For example, execute the following statement to convert the value [1,2,3] from the VARCHAR type to the JSON type, and then to the SMALLINT type:
      SELECT cast(cast('[1,2,3]' AS json) AS smallint);
      The following error message is returned:
      ERROR 1815 (HY000): [20034, 2021091814103119216818804803453190138] : Cannot cast json to smallint

CAST AS BIGINT

cast (expr AS bigint)
  • Description: converts the value specified by expr to the BIGINT type.
  • Source data types:
    • BOOLEAN
    • DECIMAL(M,D), FLOAT, INT/INTEGER, SMALLINT, or TINYINT
    • DOUBLE
    • DATE, DATETIME, TIMESTAMP, or TIME
    • VARCHAR
  • Examples:
    • Execute the following statement to convert the value 2021-09-18 from the DATE type to the BIGINT type:
      SELECT cast(date '2021-09-18' AS bigint);
      The following result is returned:
      +-----------------------------------+
      | cast(date '2021-09-18' AS bigint) |
      +-----------------------------------+
      |                          20210918 |
      +-----------------------------------+
    • Execute the following statement to convert the value -1 from the JSON type to the BIGINT type:
      SELECT cast(json '-1' AS bigint);
      The following result is returned:
      +---------------------------+
      | cast(json '-1' AS bigint) |
      +---------------------------+
      |                        -1 |
      +---------------------------+
  • Precautions:
    • If the input VARCHAR value is not a number, a value of 0 is returned after the input value is converted to the BIGINT type.
      For example, execute the following statement to convert the value a from the VARCHAR type to the BIGINT type:
      SELECT cast('a' AS bigint);
      The following result is returned:
      +---------------------+
      | cast('a' AS bigint) |
      +---------------------+
      |                   0 |
      +---------------------+
    • In the following scenarios, data is rounded after the conversion:
      • Convert data from the DOUBLE type to the BIGINT type.
        Execute the following statement to convert the value 1.23456789 from the DOUBLE type to the BIGINT type:
        SELECT cast(1.23456789 AS bigint);
        The following result is returned:
        +----------------------------+
        | cast(1.23456789 AS bigint) |
        +----------------------------+
        |                          1 |
        +----------------------------+
      • Convert data from the DECIMAL(M,D), FLOAT, INT/INTEGER, SMALLINT, or TINYINT type to the BIGINT type.
        For example, execute the following statement to convert the value 1.1 from the DECIMAL type to the BIGINT type:
        SELECT cast(1.1 AS bigint);
        The following result is returned:
        +---------------------+
        | cast(1.1 AS bigint) |
        +---------------------+
        |                   1 |
        +---------------------+
    • If the input JSON value is not a number, an error is returned when the input value is converted to the BIGINT type.
      For example, execute the following statement to convert the value {} from the JSON type to the BIGINT type:
      SELECT cast(json'{}'AS bigint);
      The following error message is returned:
      ERROR 1815 (HY000): [30013, 2021091815393219216818804803453205032] : Cannot cast '{}' to bigint

CAST AS DOUBLE

cast (expr AS double)
  • Description: converts the value specified by expr to the DOUBLE type.
  • Source data types:
    • BOOLEAN
    • DECIMAL(M,D), FLOAT, INT/INTEGER, SMALLINT, or TINYINT
    • BIGINT
    • DATE, DATETIME, TIMESTAMP, or TIME
    • VARCHAR
    • JSON
  • Example:
    Execute the following statement to convert the value 2021-09-17 from the DATE type to the DOUBLE type:
    SELECT cast( date '2021-09-17' AS double);
    The following result is returned:
    +------------------------------------+
    | cast( date '2021-09-17' AS double) |
    +------------------------------------+
    |                        2.0210917E7 |
    +------------------------------------+
  • Precautions:
    • If the input VARCHAR value is not a number, a value of 0.0 is returned after the input value is converted to the DOUBLE type.
      For example, execute the following statement to convert the value China from the VARCHAR type to the DOUBLE type:
      SELECT cast( 'China' AS double);
      The following result is returned:
      +--------------------------+
      | cast( 'China' AS double) |
      +--------------------------+
      |                      0.0 |
      +--------------------------+
    • If the input JSON value is not a number, an error is returned when the input value is converted to the DOUBLE type.
      For example, execute the following statement to convert the value {} from the JSON type to the DOUBLE type:
      SELECT cast(json '{}' AS double);
      The following error message is returned:
      ERROR 1815 (HY000): [30013, 2021091816172219216818804803453211359] : Cannot cast '{}' to double

CAST AS DATE|DATETIME|TIMESTAMP|TIME

cast (expr AS date|datetime|timestamp|time)
  • Description: converts the value specified by expr to the DATE, DATETIME, TIMESTAMP, or TIME type.
  • Source data types:
    • DECIMAL(M,D), FLOAT, INT/INTEGER, SMALLINT, or TINYINT
    • BIGINT
    • DOUBLE
    • DATE, DATETIME, TIMESTAMP, or TIME
    • VARCHAR
    • JSON
  • Example:
    Execute the following statement to convert the value 20010122000000 from the BIGINT type to the DATE type:
    SELECT cast(20010122000000 AS date);
    The following result is returned:
    +------------------------------+
    | cast(20010122000000 AS date) |
    +------------------------------+
    | 2001-01-22                   |
    +------------------------------+
  • Precautions:
    • If the input VARCHAR or BIGINT value does not conform to the TIME format, a value of NULL is returned after the conversion.
      For example, execute the following statement to convert the value a from the VARCHAR type to the TIME type:
      SELECT cast('a' AS time);
      A value of NULL is returned as shown in the following result because the input value does not conform to the TIME format:
      +-------------------+
      | cast('a' AS time) |
      +-------------------+
      | NULL              |
      +-------------------+
    • If the destination data type contains a time and a date but the corresponding information is not included in the source data, the following rules apply:
      • By default, if a time is not included in the source data, a value of 00:00:00 is returned after the conversion.
        For example, execute the following statement to convert the value 2001-1-22 from the TIMESTAMP type to the TIME type:
        SELECT cast(timestamp '2001-1-22' AS time);
        A value of 00:00:00 is returned as shown in the following result. This is because a time is not included in the source data.
        +-------------------------------------+
        | cast(timestamp '2001-1-22' AS time) |
        +-------------------------------------+
        | 00:00:00                            |
        +-------------------------------------+
      • By default, if a date is not included in the source data, the system date when the query is executed on the client is returned after the conversion.
        For example, execute the following statement to convert the value 00:00:00 from the TIME type to the DATE type:
        SELECT cast(time '00:00:00' AS date);
        The system date when the query is executed is returned as shown in the following result. This is because a date is not included in the source data.
        +-------------------------------+
        | cast(time '00:00:00' AS date) |
        +-------------------------------+
        | 2021-09-14                    |
        +-------------------------------+

CAST AS VARBINARY

cast (expr AS varbinary)
  • Description: converts the value specified by expr to the VARBINARY type.
  • Source data types:
    • BOOLEAN
    • DECIMAL(M,D), FLOAT, INT/INTEGER, SMALLINT, or TINYINT
    • BIGINT
    • DOUBLE
    • DATE, DATETIME, TIMESTAMP, or TIME
    • VARCHAR
    • JSON
  • Example:
    Execute the following statement to convert the value CHINA from the VARCHAR type to the VARBINARY type:
    SELECT cast('CHINA' AS varbinary);
    The following result is returned:
    +--------------------------------------------------------+
    | cast('CHINA' AS varbinary)                             |
    +--------------------------------------------------------+
    | 0x4348494E41                                           |
    +--------------------------------------------------------+

CAST AS VARCHAR

cast (expr AS varchar)
  • Description: converts the value specified by expr to the VARCHAR type.
  • Source data types:
    • BOOLEAN
    • DECIMAL(M,D), FLOAT, INT/INTEGER, SMALLINT, or TINYINT
    • BIGINT
    • DOUBLE
    • DATE, DATETIME, TIMESTAMP, or TIME
    • VARBINARY
      Note Only AnalyticDB for MySQL clusters of V3.1.4 or later allow you to convert data from the VARBINARY type to the VARCHAR type by using the CAST function.
    • ARRAY
    • MAP
    • JSON
  • Example:
    Execute the following statement to convert the value 2001-1-22 00:00:00 from the TIMESTAMP type to the VARCHAR type:
    SELECT cast(timestamp '2001-1-22 00:00:00' AS varchar);
    The following result is returned:
    +-------------------------------------------------+
    | cast(timestamp '2001-1-22 00:00:00' AS varchar) |
    +-------------------------------------------------+
    | 2001-01-22 00:00:00                             |
    +-------------------------------------------------+

CAST AS ARRAY

cast (expr AS array<element_type>)
  • Description: converts the value specified by expr to an array that is of the <element_type> type.
  • Data types of the input values:
    • expr: VARCHAR or JSON
    • <element_type>: TINYINT, SMALLINT, INT/INTEGER, or FLOAT
  • Example:
    Execute the following statement to convert the JSON value [1,2,3] to an array of INT type elements:
    SELECT cast( json '[1,2,3]' AS array<int>);
    The following result is returned:
    +-------------------------------------+
    | cast( json '[1,2,3]' AS array<int>) |
    +-------------------------------------+
    | [1,2,3]                             |
    +-------------------------------------+
  • Precautions: If the input VARCHAR or JSON value does not conform to the ARRAY format, an error is returned during the conversion.
    For example, execute the following statement to convert the VARCHAR value {}} to an array of FLOAT type elements:
    SELECT cast('{}}' AS array<float>);
    The following error message is returned because the input value does not conform to the ARRAY format:
    ERROR 1815 (HY000): [30013, 2021091815372119216818804803453204662] : Value cannot be cast to array(real)

CAST AS MAP

cast (expr AS map<element_type_1,element_type_2>)
  • Description: converts the value specified by expr to a map that maps the <element_type_1> type to the <element_type_2> type.
  • Data types of the input values:
    • expr: VARCHAR
    • <element_type_1>: BOOLEAN, DECIMAL, DOUBLE, FLOAT, BIGINT, INT/INTEGER, SMALLINT, TINYINT, or VARCHAR
    • <element_type_2>: BOOLEAN, DECIMAL, DOUBLE, FLOAT, BIGINT, INT/INTEGER, SMALLINT, TINYINT, VARCHAR, ARRAY, JSON, or MAP
  • Example:
    Execute the following statement to convert the VARCHAR value {"1":"a"} to a map that maps a VARCHAR element to the VARCHAR type:
    SELECT cast('{"1":"a"}' AS map<varchar,varchar>);
    The following result is returned:
    +-------------------------------------------+
    | cast('{"1":"a"}' AS map<varchar,varchar>) |
    +-------------------------------------------+
    | {"1":"a"}                                 |
    +-------------------------------------------+
  • Precautions: If the input VARCHAR value does not conform to the MAP format, an error is returned during the conversion.
    For example, execute the following statement to convert the VARCHAR value [a,b,c] to the MAP type:
    SELECT cast('[a,b,c]' AS map<varchar,varchar>);
    The following error message is returned:
    ERROR 1815 (HY000): [30013, 2021091815562519216818804803453207833] : Value cannot be cast to map(varchar,varchar)

CAST AS JSON

cast (expr AS json)
  • Description: converts the value specified by expr to the JSON type.
  • Source data types:
    • BOOLEAN
    • DECIMAL(M,D), FLOAT, INT/INTEGER, SMALLINT, or TINYINT
    • BIGINT
    • DOUBLE
    • VARCHAR
    • ARRAY
  • Examples:
    • Execute the following statement to convert the value {} from the VARCHAR type to the JSON type:
      SELECT cast('{}' AS json);
      The following result is returned:
      +--------------------+
      | cast('{}' AS json) |
      +--------------------+
      | {}                 |
      +--------------------+
    • Execute the following statement to convert the value 0 from the BIGINT type to the JSON type:
      SELECT cast( bigint '0' AS json);
      The following result is returned:
      +---------------------------+
      | cast( bigint '0' AS json) |
      +---------------------------+
      | 0                         |
      +---------------------------+
  • Precautions: If the input VARCHAR value does not conform to the JSON format, an error is returned during the conversion.
    For example, execute the following statement to convert the value {}} from the VARCHAR type to the JSON type:
    SELECT cast('{}}' AS json);
    The following error message is returned:
    ERROR 1815 (HY000): [30014, 2021091417335219216818804803453259705] : Cannot convert '{}}' to JSON