2
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

Martin Smith
  • 438,706
  • 87
  • 741
  • 845

1 Answers1

2

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.

Ted Hopp
  • 232,168
  • 48
  • 399
  • 521
  • 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) – Martin Smith Feb 09 '11 at 18:42