Geek Answers Handbook

Working with binary data in MySQL

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 look the same (screenshot below).

So why different results for col3 and col4?

Screenshothot

+2
mysql varbinary
source share
1 answer

I think this is related to data types. BinaryData has an integer data type, but substr (BinaryData, 1,4) expects a string. CAST is then confused with the result. In addition, CAST parses strings using base 10, so you need a little extra work. Try the following:

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

This is a monster, but he should give you what you want.

+1
source share

More articles:


All Articles