• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2449
  • Last Modified:

mysql: convert 2 bytes from blob to integer


I have a BLOB field and i want to take 2 bytes from it and use them as an integer.  How can i do this?  

select substr(blobfield,195,2) as value1 from mytable;

the 2 bytes on position 195 and 196 are an integer that was stored inside a blob field.

How can i get the integer back in mysql, so i can perform a where / having on this value ?

1 Solution
Aleksandar BradarićSoftware DeveloperCommented:
Depending on what you exactly have in the BLOB you could try either to get the ASCII values of each byte and calculate the integer:
SELECT ascii(substr(blobfield, 2, 1)) * 256 + ascii(substr(blobfield, 3, 1)) as myNumber FROM myBlob;

Or if you have number characters, then you could try:
SELECT (ascii(substr(blobfield, 2, 1)) - 48) * 10 + (ascii(substr(blobfield, 3, 1)) - 48) as myNumber FROM myBlob;

Hope it helps.
cleaverXAuthor Commented:
first part * 256 + second part does the job.
Strange that there is no working cast for this... (not that I know) ?

Thanks for the solution leannonn !

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now