mysql: convert 2 bytes from blob to integer

Posted on 2007-07-26
Last Modified: 2007-12-19

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 ?

Question by:cleaverX
    LVL 17

    Accepted Solution

    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.

    Author Comment

    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

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Join & Write a Comment

    As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
    I use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
    how to add IIS SMTP to handle application/Scanner relays into office 365.
    This video is in connection to the article "The case of a missing mobile phone (". It will help one to understand clearly the steps to track a lost android phone.

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now