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?

Screenshot

Ted Hopp · Accepted Answer · 2011-02-09 18:54:21Z

3

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.

edited Feb 9, 2011 at 18:54

answered Feb 9, 2011 at 17:29

Ted Hopp

235k48 gold badges412 silver badges533 bronze badges

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

1 Comment

Martin Smith Over a year ago

Yes. That seems to work and the explanation makes sense. Thanks! (+1) A slight amend though I used CAST(CONV(substr(HEX(BinaryData),1,8), 16, 10) AS SIGNED) (Your answer is currently missing the length of the substring)

Collectives™ on Stack Overflow

Working with Binary Data in MySQL

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related