Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2014
    Posts
    3

    Unanswered: MySQL convert hex to double

    Which aproach should be taken in MySQL to convert from HEX (8 Bytes) to a double value?

    To convert from HEX to INT I use CONV(hex_value,16,10). But in order to converto to double I've no idea.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    If you are using the EXACT same platform (hardware, OS, MySQL version and patches) then you can use:
    Code:
    Cast(HexExpression AS DOUBLE)
    If you are not using EXACTLY the same platform, there isn't any guaranteed solution to the problem.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Sep 2014
    Posts
    3
    Some one proposed the following:

    Code:
    CREATE FUNCTION Convert_Hex_2_Double(HexByteArray TEXT) 
      RETURNS DOUBLE
    BEGIN
      
    	DECLARE vBINARY VARCHAR(64);
    	DECLARE vBUFFER VARCHAR(52);
    	DECLARE vBUFFER_1 CHAR(1);
    	DECLARE vBUFFER_2 VARCHAR(11);
    	DECLARE vTOTAL INT;
    	DECLARE X INT;
    	DECLARE vEXP INT;
    	DECLARE vEXP_2 INT;
    	DECLARE vDOUBLE DOUBLE;
        
    	SET vTOTAL  =  LENGTH(HexByteArray) ;
    	SET vBINARY = conv(HexByteArray,16,2);
    	
    	IF vTOTAL <= 8 and vBINARY > 0 THEN -- float
    		SET vBINARY = LPAD (vBINARY , 32, '0');
    		SET vBUFFER_2 =  substr (vBINARY, 2,8 ); 
    		SET vBUFFER  =  substr (vBINARY, 10,23); 
    		SET vEXP_2 = 127;
    	ELSEIF vTOTAL = 16 and vBINARY > 0 THEN -- double
    		SET vBINARY = LPAD (vBINARY , 64, '0');
    		SET vBUFFER_2 =  substr (vBINARY, 2,11 );
    		SET vBUFFER  =  substr (vBINARY, 13,52 );	
    		SET vEXP_2 = 1023;
    	ELSE 
    		RETURN 0;
    	END IF;
    		
    	SET vBUFFER_1 =  substr (vBINARY, 1,1 ); -- (0=positivo ou 1=negativo)
    	SET vTOTAL  =  LENGTH(vBUFFER);
    	SET vEXP  =   conv(vBUFFER_2, 2, 10);
    	
    	SET vDOUBLE  =  0;
    	SET X  =  1;
    	
    	WHILE (X <= vTOTAL) DO
    	  	  IF substr(vBUFFER, X , 1 ) = '1' THEN 
    			 SET vDOUBLE  =  vDOUBLE + ( 1 / POWER(2,X) );        
    		  END IF;
    		  SET X  =  X + 1; 
    	END WHILE;
    
    	SET vDOUBLE  =  (vDOUBLE + 1) * POWER( 2 ,  ( vEXP ) - vEXP_2);
    
    	IF vBUFFER_1 = 1 THEN 
    	   SET vDOUBLE  =  vDOUBLE * - 1;
    	END IF;
    	 
        RETURN vDOUBLE;
    
    END;
    Is it a good idea?

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •