CODEWITHSUNDEEP
mysqlhexblobextract
Bart V
Bart V

Reputation: 151

How to extract Hexadecimal values from BLOB-column in MySQL

I'm trying to extract float-values from a BLOB column written in single-precision 32 bit hexadecimal. To clarify in more detail I build below example similar to the Mysql-db I'm working with: i.e., a table with a BLOB column which contains the value '42f00000'. Objective would be to have a function which extracts the value '120' from it.

Example dataset would be:

CREATE TABLE MyTable (FirstColumn BLOB);

INSERT INTO MyTable (FirstColumn) VALUES (0x42f00000); #Representing value 120 in single-precision 32bit Hex

SELECT *
FROM MyTable;

The output of the latter 'SELECT *' would be this: enter image description here

I'm challenge with writing a function which can do this for the entire column. I tried what I found on the internet but without success: i.e., 

SELECT UNHEX(FirstColumn)
FROM MyTable;
## NULL-result

SELECT CONV(FirstColumn, 10,16)
FROM MyTable;
## 0 as result

Hope anyone has similar experiences?

Upvotes: 2

Views: 15694

Answers (1)

Bart V
Bart V

Reputation: 151

I was able to do step one; i.e. extract the HEX value from the BLOB. 

SELECT CONCAT("0x",HEX(CAST(FirstColumn AS CHAR(10000) CHARACTER SET utf8)))
FROM MyTable;

This code Returns '0x42F00000' as a String

Next step would be to decode this HEX value (which is a string) now to a float through single-precision conversion (not ASCI). Similar to this type of conversion: https://www.h-schmidt.net/FloatConverter/IEEE754.html

Any ideas?

Upvotes: 7

Related Questions