How to extract Hexadecimal values from BLOB-column in MySQL

Question

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:

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?

Bart V · Accepted Answer · 2017-01-11 12:31:04Z

7

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?

answered Jan 11, 2017 at 12:31

Bart V

1511 gold badge1 silver badge9 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to extract Hexadecimal values from BLOB-column in MySQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related