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

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