get partial data from BLOB as hex

Question

I have a long binary written to MySQL as long BLOB. When I read the BLOB back from the DB by my PHP app, I would like to take the first 12 bytes only as hex. I successfully converted the BLOB to hex by using bin2hex(BLOB) but don't know how to ignore the data over the first 12 bytes.

peterm · Accepted Answer · 2014-01-29 21:32:52Z

4

Are you looking for something this?

SELECT HEX(LEFT(data, 12)) partial
  FROM table1

Here is SQLFiddle demo

The BLOB and TEXT Types

BLOB values are treated as binary strings (byte strings).

Therefore you can grab first 12 bytes with LEFT() and then convert them with HEX()

I believe in Codeigniter you can do something like this

$result = $this->db
               ->select('HEX(LEFT(data, 12)) AS partial', FALSE)
               ->from('table1')
               ->get();

edited Jan 29, 2014 at 21:32

answered Jan 29, 2014 at 21:24

peterm

93k16 gold badges157 silver badges162 bronze badges

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

1 Comment

Simon Over a year ago

I am using CodeIgniter to SELECT. I will look to see how this can be done there

Collectives™ on Stack Overflow

get partial data from BLOB as hex

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related