0

I have a table which has a varbinary column I would like to select all rows where the first byte of that data is 0x0b

Is there a way to for a query which will select based on the compare of the first byte?

Maybe using a 'like'?
Thanks

vbbartlett
  • 179
  • 2
  • 14
  • What a great way to inefficiently design the database. Congratulations, you're one of millions who have to deal with absolutely horrible way of modelling and storing data. Since what I'm writing isn't really helpful, I just hope that you won't have too many problems dealing with such dreaded task. Good luck! – N.B. Aug 22 '14 at 15:20

1 Answers1

0

Not in MySQL (which I'm assuming you are using based on the tags).

If you have the ability to create tables, I would recommend creating a sharding table that separates the bits you would need to query against into individual tinyint columns. You would need to handle the sharding outside of the DBMS since triggers wont be able to help, but having a sharding table would certainly make these types of queries much faster.

If not, then you are going to have to do a query for greater than & less than and then cycle through on a bitwise operation.

Both of these solutions assume to have access to the requesting system. If you are strictly DBA, then no.

BayssMekanique
  • 894
  • 1
  • 14
  • 27
  • damn... yeah All i have access to is that blob. Using the Mysql Work bench I am able to look at the binary data but I would really like to isolate the rows that have a particular value in the first byte. - I need to change this value as the client app that inputs this data won't be updated for 2 weeks... – vbbartlett Aug 22 '14 at 17:54
  • Honestly, I would write an update script in bash or shell (if you have access to either). You can run the query and either write to a file, or pipe the output into a quick `awk | grep | sed` and feed back into another update query. – BayssMekanique Aug 22 '14 at 18:00
  • If you only need to do this once, you can do a SQL dump, import the database into MSSQL, perform a bitwise `UPDATE` there, then dump again and import back into MySQL. – BayssMekanique Aug 22 '14 at 18:03