Is it possible to cast a DECIMAL to DOUBLE in MySQL?

Question

I know all the numerical implications, that is, the possible rounding issues inherent to floating point formats, but in my case I have DECIMAL columns in MySQL that I want to convert to DOUBLE straight in the MySQL query rather than down stream.

Could anyone help?

Answer 1

SELECT my_decimal_field + 0E0 FROM my_table

The following quotes from MySQL manual explain how this works:

9.1.2 Numeric Literals

Number literals include exact-value (integer and DECIMAL) literals and approximate-value (floating-point) literals.

Numbers represented in scientific notation with a mantissa and exponent are approximate-value numbers.

12.22.3 Expression Handling

Handling of a numeric expression depends on what kind of values the expression contains:

• If any approximate values are present, the expression is approximate and is evaluated using floating-point arithmetic.

Answer 2

Because of the limitations of the built in CAST function in MySQL, it is only possible to convert DECIMAL to DOUBLE with your own user defined cast function.

Sample use case:

SELECT castDecimalAsDouble(0.000000000000000000100000000000);

Result: 1e-23

CREATE DEFINER=`root`@`localhost` FUNCTION `castDecimalAsDouble`(
decimalInput DECIMAL(65,30) ) RETURNS double
DETERMINISTIC
BEGIN
DECLARE doubleOutput DOUBLE;

SET doubleOutput = decimalInput;

RETURN doubleOutput;
END