Type Conversion Rules

Most expressions or functions in SQL expect a particular data type, for example, a CHAR or VARCHAR data type in a string function SUBSTR[ING]. If you wish to use a different data type, it must be converted first using the explicit conversion functions. For more information, see Conversion Functions.

Data types can be converted either explicitly or implicitly. If explicit conversion functions are not used, then the system attempts to perform an implicit conversion. If an implicit function is not possible or if one single value is not successfully converted during the computation, the system displays an error message.

The following table provides you with an overview of the permissible implicit conversions.

SOURCE TYPE
TARGET TYPE
DECIMAL
DOUBLE
BOOLEAN
DATE/TIMESTAMP(p) [WITH LOCAL TIME ZONE]
INTERVAL YEAR(p) TO MONTH
INTERVAL DAY(lfp) TO SECOND(fsp)
GEOMETRY
HASHTYPE
CHAR/VARCHAR

DECIMAL

  

DOUBLE

  

BOOLEAN

  

DATE/TIMESTAMP(p) [WITH LOCAL TIME ZONE]

  

INTERVAL YEAR(p) TO MONTH

  

INTERVAL DAY(p) TO SECOND(fp)

  

GEOMETRY

  

HASHTYPE

  

CHAR/VARCHAR

  

= Implicit conversion always works

= Implicit conversion possible, but errors may occur if single values cannot be converted

= Implicit conversion is not possible

To maintain the performance improvements of using the HASHTYPE data type, implicit conversion between HASHTYPE and CHAR/VARCHAR data types is not supported in joins.

Usage notes

  • When converting DECIMAL to DOUBLE, inaccuracies may occur with rounding.

  • When converting a DECIMAL to a DECIMAL with a smaller scale (including the INTEGER alias), the value is rounded to the specified scale. For example, SELECT CAST(5.56 AS DECIMAL(3,1)); returns 5.6.

  • When converting DECIMAL or DOUBLE to BOOLEAN, 1 is transformed to TRUE, 0 is transformed to FALSE, and NULL is transformed to NULL. For other data types, you can use the compatible representations of the values 1 and 0. For example, 1 = 1.00 (DECIMAL(3.2)) = 1.0 (DECIMAL(2.1)).

  • When converting from BOOLEAN to DECIMAL or DOUBLE, TRUE is transformed to 1, FALSE is transformed to 0, and NULL is transformed to NULL.

  • When converting from BOOLEAN to CHAR(n) or VARCHAR(n), TRUE is transformed to 'TRUE', and FALSE is transformed to 'FALSE'.

  • Conversion from CHAR(n) or VARCHAR(n) to another data type is successful only if:

    • The data to be converted conforms to the target data type

    • All characters exist in the target character set

    • The value n is large enough

  • When converting from CHAR(n) or VARCHAR(n) to BOOLEAN, you can use the strings '0', 'F', 'f', 'N', 'n', or 'FALSE' (case-insensitive) for the value FALSE, and '1', 'T', 't', 'y', 'Y', or 'TRUE' (case-insensitive) for the value TRUE.

  • When converting from CHAR(n) or VARCHAR(n) to DATE or TIMESTAMP, you must take into account the current format model. For more information, see Date/Time Format Model.

  • When there are operations with multiple operands (for example, the operators +,-,/,*), Exasol implicitly converts the operand to the data type with higher precedence (for example, DOUBLE is higher than DECIMAL) before executing the operation. This rule is also called numeric precedence.

  • Conversion of GEOMETRY object with different spacial reference systems is not supported.

  • When converting between GEOMETRY types and VARCHAR/CHAR data types, the maximum significant digits used when casting is defined with the ST_MAX_DECIMAL_DIGITS parameter and can be changed using the ALTER SYSTEM or ALTER SESSION statements. Note that the default value is 16, and the allowed range is <1,16>.

    For example, the table for POINT(40.7267, -74.0345) casted to string with precisions 1 to 16:

    ST_MAX_DECIMAL_DIGITS Geometry as a string
    1 POINT(4e+01 -7e+01)
    2 POINT(41 -74)
    3 POINT(40.7 -74)
    4 POINT(40.73 -74.03)
    5 POINT(40.727 -74.034)
    6 POINT(40.7267 -74.0345)
    7 POINT(40.7267 -74.0345)
    8 POINT(40.7267 -74.0345)
    9 POINT(40.7267 -74.0345)
    10 POINT(40.7267 -74.0345)
    11 POINT(40.7267 -74.0345)
    12 POINT(40.7267 -74.0345)
    13 POINT(40.7267 -74.0345)
    14 POINT(40.7267 -74.0345)
    15 POINT(40.7267 -74.0345)
    16 POINT(40.7267 -74.03449999999999)
Example

In the following example, implicit conversion is done to insert the BOOLEAN entry into the DECIMAL column of the created table.

CREATE TABLE t(d DECIMAL);
-- Implicit conversion from BOOLEAN to DECIMAL
INSERT INTO t VALUES TRUE, NULL, FALSE;

SELECT * FROM t;