Working with Binary Data in MySQL

Question

SELECT 
      0x0000987C As col1,
      substr(BinaryData,1,4) As col2,
      CAST(0x0000987C  AS SIGNED) As col3,
      CAST(substr(BinaryData,1,4)  AS SIGNED) As col4
FROM
(
SELECT 0x0000987C00000000 AS BinaryData
) d

Returns

col1  col2   col3  col4
----  ----  -----  ----
BLOB  BLOB  39036   0

When I look at the BLOB viewer for col1 and col2 they both appear identical (screenshot below).

So why the different results for col3 and col4?

Answer 1

I think it has to do with data types. BinaryData has an integer data type, but substr(BinaryData,1,4) expects a string. CAST then gets confused with the result. Also, CAST parses strings using base 10, so you need to a little bit of extra work. Try this:

CAST(CONV(substr(HEX(BinaryData),1,8), 16, 10)  AS SIGNED) As col4

It's a monster, but it should give you what you want.