How do we perform operations on SQL binary data type?

Question

Hi all is it possible to do arithmetic operations on MySQL / SqlServer binary data type?

For example, I have 256-bit values stored within a column.

And I want to select values between 10000000000 (1024) and 10011010100111110101 (633333).

Do databases provide this functionality?

Or is storing it as 4 64-bit integer column the only way to select a range from x to y?

What is the the real world use case you are trying to solve with every 5th digit offed? — Michael Durrant
– Michael Durrant, Commented Oct 16, 2011 at 0:22
@MichaelDurrant for the second example, it is a question to see if there are inbuilt mysql functions that could achieve it. but i'm sure you can find real world use cases for the first question — Pacerier
– Pacerier, Commented Oct 16, 2011 at 0:50
I might not be reading it right, but does BETWEEN do what you want? (ref: tutorialspoint.com/mysql/mysql-between-clause.htm) — jefflunt
– jefflunt, Commented Oct 16, 2011 at 1:24
@normalocity yes effectively what I want is a between that works for binary types. — Pacerier
– Pacerier, Commented Oct 16, 2011 at 15:42

Thomas W · Accepted Answer · 2013-09-09 05:38:00Z

2

'Binary datatype'/ binary object/ BLOB is opaque to the database, by definition. If you use this type it's up to the application to process it.

If you want the database to process/index/query your data, pick a datatype it understands. For SQL Server, BIGINT is 8 bytes (64 bits) and NUMERIC can go to 38 digits (~ 126 bits). In MySQL, DECIMAL/NUMERIC can go to 64 or 65 digits (~ 212 bits).

And if you do actually have a real requirement here, you can "factor out" the actual significant segments into their own columns & query on those.

In the most blunt & uninformed workaround case, that could be dividing your 256-bit chunk into 2 or 4 words; but if you have any kind of real requirement, you should be able to identify more specific sections which are of business interest.

answered Sep 9, 2013 at 5:38

Thomas W

14.2k4 gold badges64 silver badges77 bronze badges

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

Collectives™ on Stack Overflow

How do we perform operations on SQL binary data type?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related