Convert MySQL DECIMAL to hexadecimal of floating point IEEE representation

0
votes

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)?

3
mysqlfloating-pointhexieee-754sql-function
I would convert 10.0 To A and 10.9 also to A. I am nit sure, what You exactly want to do. could you give some examples please – nbk
@nbk I want 10.9 to become 412E6666 assuming it's in a single precision floating point (float). 412E6666 is the hexadecimal of the IEEE representation of 10.9 in binary. – Winter
You want a Stored Function (or equivalent code) that takes the string "412E6666" and comes back with a value that, if stored into a FLOAT, would be 10.9? (I know how to do it in PHP; I'll scratch my head on doing it in MySQL.) Note: type type DECIMAL is stored differently than FLOAT. – Rick James
@RickJames No I want the other way around. I have the DECIMAL and I want to converr it to that 412E6666 that I'll be able to read later (in Java). I basically want the IEEE (the ones we could easily do in Java, PHP or C) way of storing it. I don't actually care about how MySQL stores its FLOAT. I've read more about float representation and we could find the biased exponent, sign bit and mantissa easily but putting those together into a INTEGER would be challenging without binary operators. (<<, & and | mainly) – Winter
@Winter - MySQL does have <<, & and | (with BIGINT), but it does not have any way to violate type checking to get between datatypes FLOAT and BIGINT. – Rick James

3 Answers

1
votes

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
1
votes

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 ;
-1
votes

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.