Cast number of bytes from blob field to number

Question

I have a table with one blob field named bindata. bindata always contains 7 bytes. First four of them is an integer (unsigned I think, db is not mine).

My question is how can I select only the first four bytes from bindata and convert them to a number?

I am new in mySQL but from the documentation I see that I may have to use the conv function by doing something like this:

SELECT CONV(<Hex String of first 4 bytes of bindata>,16,10) as myNumber

But I don't have a clue on how to select only the first four bytes of the blob field. I am really stuck here.

Thanks

invisal · Accepted Answer · 2013-09-17 03:58:53Z

1

You can use string function to get partial of byte in the blob. For example:

SELECT id, 
       ((ORD(SUBSTR(`data`, 1, 1)) << 24) +
        (ORD(SUBSTR(`data`, 2, 1)) << 16) +
        (ORD(SUBSTR(`data`, 3, 1)) << 8) +
         ORD(SUBSTR(`data`, 4, 1))) AS num
FROM test;

Here is Demo in SQLFiddle

edited Sep 17, 2013 at 3:58

answered Sep 17, 2013 at 3:50

invisal

11.2k4 gold badges36 silver badges54 bronze badges

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

Collectives™ on Stack Overflow

Cast number of bytes from blob field to number

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