Convert MySQL DECIMAL to hexadecimal of floating point IEEE representation

Question

I'm trying to add hex data to a hex string and I need to add floating point numbers to that string, using their IEEE representation. For integers, this is simple enough:

SET params = CONCAT(params,
    CASE
        WHEN type IS 'int' THEN LPAD(HEX(CAST(value AS SIGNED INTEGER)), 8, '0')
        WHEN type IS 'long' THEN LPAD(HEX(CAST(value AS SIGNED INTEGER)), 16, '0')
        WHEN type IS 'double' THEN LPAD(HEX(CAST(value AS DECIMAL)), 16, '0')
        WHEN type IS 'float' THEN LPAD(HEX(CAST(value AS DECIMAL)), 8, '0')
        ELSE 0
    END);

Where value is the VARCHAR of a number and params is a VARCHAR containing a hex string. This trick works for integers but for decimal, it truncates the decimal part and converts the integer part as an hexadecimal integer. How can I convert value to the hexadecimal of the IEEE floating point representation of the decimal number, given the size of the decimal is fixed (either java float or double)?

Answer 1

While @nbk's answer had the right idea, his implementation didn't work for sub normal numbers and an implementation for double precision floating point was missing. Here's a simplified version of his answer supporting single and double precision and working with subnormal numbers. I personally didn't need to convert commas to decimal points because my database language is in english, but you might have to in your case.

DELIMITER //

DROP FUNCTION IF EXISTS FLOAT_BIN//

CREATE FUNCTION FLOAT_BIN(number FLOAT, places INT)
RETURNS TEXT CHARSET utf8mb4 DETERMINISTIC
BEGIN
    DECLARE whole INT;
    DECLARE dec1 FLOAT;
    DECLARE res TEXT;

    SET whole = FLOOR(number);
    SET dec1 = number - whole;

    SET res = CONCAT(BIN(whole), '.');

    WHILE 0 < places DO
        SET dec1 = dec1 * 2;

        SET whole = FLOOR(dec1);
        SET dec1 = dec1 - whole;

        SET res = CONCAT(res, whole);
        SET places = places - 1;
    END WHILE;
    RETURN res;
END//

DROP FUNCTION IF EXISTS IEEE754;

CREATE FUNCTION IEEE754(n FLOAT) RETURNS CHAR(8)
BEGIN
    DECLARE sign INT;
    DECLARE whole VARCHAR(256);
    DECLARE dec1 VARCHAR(256);
    DECLARE exponent INT;
    DECLARE mantissa VARCHAR(256);

    # check if number is negative
    SET sign = 0;
    IF n < 0 THEN
        SET sign = 1;
        SET n = n * -1;
    END IF;

    # convert float to binary
    SET dec1 = FLOAT_BIN(n, 256); # good upper bound is twice the max exponent

    # separate the decimal part
    # and the whole number part
    SET whole = SUBSTRING_INDEX(dec1, '.', 1);
    SET dec1 = SUBSTRING_INDEX(dec1, '.', -1);

    # calculating the exponent(E)

    IF n >= 1 THEN
        SET exponent = LENGTH(whole) - 1;
        SET mantissa = CONCAT(SUBSTR(whole, 2), dec1);
    ELSE
        SET exponent = -1;
        WHILE SUBSTR(dec1, 1, 1) = '0' AND exponent > -127 DO
            SET exponent = exponent - 1;
            SET dec1 = SUBSTR(dec1, 2);
        END WHILE;

        IF exponent = -127 THEN
            SET mantissa = dec1;
        ELSE
            SET mantissa = SUBSTR(dec1, 2);
        END IF;
    END IF;

    RETURN CONV(CONCAT(sign, LPAD(BIN(127 + exponent), 8, '0'), RPAD(mantissa, 23, '0')), 2, 16);
END//

DROP FUNCTION IF EXISTS IEEE754_DOUBLE;

CREATE FUNCTION IEEE754_DOUBLE(n FLOAT) RETURNS CHAR(16)
BEGIN
    DECLARE sign INT;
    DECLARE whole VARCHAR(4096);
    DECLARE dec1 VARCHAR(4096);
    DECLARE exponent INT;
    DECLARE mantissa VARCHAR(4096);

    # check if number is negative
    SET sign = 0;
    IF n < 0 THEN
        SET sign = 1;
        SET n = n * -1;
    END IF;

    # convert float to binary
    SET dec1 = FLOAT_BIN(n, 4096);

    # separate the decimal part
    # and the whole number part
    SET whole = SUBSTRING_INDEX(dec1, '.', 1);
    SET dec1 = SUBSTRING_INDEX(dec1, '.', -1);

    # calculating the exponent(E)

    IF n >= 1 THEN
        SET exponent = LENGTH(whole) - 1;
        SET mantissa = CONCAT(SUBSTR(whole, 2), dec1);
    ELSE
        SET exponent = -1;
        WHILE SUBSTR(dec1, 1, 1) = '0' AND exponent > -1023 DO
                SET exponent = exponent - 1;
                SET dec1 = SUBSTR(dec1, 2);
            END WHILE;

        IF exponent = -1023 THEN
            SET mantissa = dec1;
        ELSE
            SET mantissa = SUBSTR(dec1, 2);
        END IF;
    END IF;

    RETURN CONV(CONCAT(sign, LPAD(BIN(1023 + exponent), 11, '0'), RPAD(mantissa, 52, '0')), 2, 16);
END//

DELIMITER ;

Answer 2

So after some time i got a solution, that consists of two functions and one Stored procedure(this can also be implemented as function but i like procedures.

This is based on this python script, https://www.geeksforgeeks.org/python-program-to-represent-floating-number-as-hexadecimal-by-ieee-754-standard/

and uses following approach

• Check whether the number is positive or negative. Save the sign as 0 for positive and 1 for negative, and then convert the number into positive if it is negative.

• Convert the floating point number to binary.

• Separate the decimal part and the whole number part.
• Calculate the exponent(E) and convert it to binary.
• Find the mantissa.
• Concatinate the sign of mantissa, exponent and the mantissa. Convert it into hexadecimal.

First the used functions.

DELIMITER $$
CREATE DEFINER=`root`@`localhost` FUNCTION `decimal_converter`(num INTEGER) RETURNS decimal(10,10)
    DETERMINISTIC
BEGIN
   DECLARE outnum DECIMAL(10,10);

   SET outnum = num/10;
   label1: WHILE outnum > 1 DO
     SET outnum = outnum / 10;
   END WHILE label1;
RETURN outnum;
END$$
DELIMITER ;

AND also needed

DELIMITER $$
CREATE DEFINER=`root`@`localhost` FUNCTION `float_bin`(number float
, places INT) RETURNS text CHARSET utf8mb4
    DETERMINISTIC
BEGIN
    DECLARE whole INT;
    DECLARE dec1  INT;
    DECLARE res TEXT;
    IF places = NULL THEN SET places = 3; END IF;
    SELECT
    SUBSTRING_INDEX(REPLACE(CAST(TRIM(TRAILING '.' FROM TRIM(TRAILING '0' from (number))) as CHAR(90)),',','.'), '.', 1) INTO @a;
    SELECT
    SUBSTRING_INDEX(REPLACE(CAST(TRIM(TRAILING '.' FROM TRIM(TRAILING '0' from (number))) as CHAR(90)),',','.'), '.', -1) iNTO @b;
    SET whole = @a;
    SET dec1 = @b ;
    SET res = BIN(whole);
    SET res = CONCAT(res , '.');
    while 0 < places do

        SELECT
          SUBSTRING_INDEX(REPLACE(CAST(TRIM(TRAILING '.' FROM TRIM(TRAILING '0' from (decimal_converter(dec1) * 2))) as CHAR(90)),',','.'), '.', 1) INTO @a;
       SELECT
          SUBSTRING_INDEX(REPLACE(CAST(TRIM(TRAILING '.' FROM TRIM(TRAILING '0' from (decimal_converter(dec1) * 2))) as CHAR(90)),',','.'), '.', -1) INTO @b;
        SET whole = @a;
        SET dec1 = @b;
        SET res = CONCAT(res , whole) ;
        SET places=places-1;
  end while;  
RETURN res;
END$$
DELIMITER ;

AND the final stored procedure

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `IEEE754`(
IN n FLOAT
)
BEGIN
    DECLARE sign Integer;
    DECLARE whole TEXT;
    DECLARE dec1  TEXT;
    DECLARE p INT;
    DECLARE exponent  INT;
    DECLARE tmpstr  VARCHAR(60);
    DECLARE exponent_bits INT;
    DECLARE exponent_bitsstr  TEXT;
    DECLARE mantissa TEXT;
    DECLARE finally TEXT;
    DECLARE  hexstr TEXT;
    #check if number is negative
    SET sign = 0;
    IF n < 0 Then
        SET sign = 1;
        SET n = n  * -1;
    END IF;
    SET p = 30 ;
    # convert float to binary 
    SET dec1 = float_bin (n, p);
    # separate the decimal part 
    # and the whole number part 
    SELECT
      SUBSTRING_INDEX(REPLACE(CAST(dec1 as CHAR(90)),',','.'), '.', 1) INTO @a;
    SELECT
      SUBSTRING_INDEX(REPLACE(CAST(dec1 as CHAR(90)),',','.'), '.', -1) iNTO @b;
    SET whole = @a;
    SET dec1 = @b ;
    # calculating the exponent(E) 
    SET tmpstr = CAST(whole as CHAR(60));
    SET exponent = LENGTH(tmpstr) - 1;
    SET exponent_bits = 127 + exponent;
    SET exponent_bitsstr = BIN(exponent_bits);

    # finding the mantissa 
    SET  mantissa = SUBSTRING(tmpstr,2,exponent);
    SET  mantissa = CONCAT(mantissa,dec1);
    SET  mantissa = SUBSTRING(mantissa,1,23); 

    # the IEEE754 notation in binary 
    SET finally = CONCAT(sign,exponent_bitsstr,mantissa );
    SET hexstr = CONV(finally,2,16);
    SELECT hexstr;
END$$
DELIMITER ;

This gives you following result:

call IEEE754(263.3);
4383A666
call IEEE754(10.9);
412E6666

Answer 3

It can't be done in MySQL. Not even the brand new (8.0.17):

CAST(UNHEX('412E6666') AS FLOAT)

Also, there is no way to go the other direction, since any form of CAST or HEX will take only a string, that is, "10.9", not the bits/bytes/hex of 10.9.

If you can back up one step, maybe the bigger goal can be achieved in some other way.