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
|
INTERVAL YEAR[(p)] TO MONTH
|
INTERVAL DAY[(p)] TO SECOND[(fp)]
|
GEOMETRY
|
HASHTYPE
|
CHAR/VARCHAR
|
---|---|---|---|---|---|---|---|---|---|---|
DECIMAL |
||||||||||
DOUBLE |
||||||||||
BOOLEAN |
||||||||||
DATE/TIMESTAMP |
||||||||||
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 |
Implicit conversions between HASHTYPE
and CHAR/VARCHAR
data types are not supported in joins in order to maintain the performance improvements of using the HASHTYPE
data type.
Conversion Considerations
Below are some of the conversion behaviors exhibited by their corresponding data types:
- When converting
DECIMAL
toDOUBLE
, inaccuracies may occur with rounding. -
When converting a
DECIMAL
to aDECIMAL
with a lower scale (including theINTEGER
alias), the value is rounded to the scale specified. For example,SELECT CAST(5.56 AS DECIMAL(3,1));
will return5.6
. - When converting
DECIMAL
orDOUBLE
toBOOLEAN
,1
is transformed toTRUE
,0
is transformed toFALSE
, andNULL
is transformed toNULL
. For other data types, you can use the compatible representations of the values1
and0
. For example,1 = 1.00 (DECIMAL(3.2)) = 1.0 (DECIMAL(2.1))
. - When converting from
BOOLEAN
toDECIMAL
orDOUBLE
,TRUE
is transformed to1
,FALSE
is transformed to0
, andNULL
is transformed toNULL
. - When converting from
BOOLEAN
toCHAR(n)
orVARCHAR(n)
,TRUE
is transformed to'TRUE'
, andFALSE
is transformed to'FALSE'
. - The conversion of
GEOMETRY
object with different spacial reference systems is not supported. - The conversion from
CHAR(n)
orVARCHAR(n)
functions if n is large enough and all characters exist in the target character set. - The conversion from
CHAR(n)
orVARCHAR(n)
to another data type is successful only if the data to be converted conforms to the target data type. - When converting from
CHAR(n)
orVARCHAR(n)
toDATE
orTIMESTAMP
, you must take into account the current format model. For more information , see Date/Time Format Model section. - When converting from
CHAR(n)
orVARCHAR(n)
toBOOLEAN
, you can use strings'0'
,'F'
,'f'
'N'
'n'
or'FALSE'
(case-insensitive) for valueFALSE
and strings'1'
,'T'
,'t'
'y'
'Y'
or'TRUE'
(case-insensitive) for valueTRUE
. - 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 thanDECIMAL
) before executing the operation. This rule is also called numeric precedence. - When converting between
GEOMETRY
types andVARCHAR/CHAR
data types, the maximum significant digits used when casting is defined with theST_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) is casted to string with different precisions, as shown below:
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 conducted to insert the BOOLEAN
entry into the DECIMAL
column of the created table.