May 16, 2016 at 9:37 pm
Comments posted to this topic are about the item Binary Compare
May 16, 2016 at 11:20 pm
Hex comparisons! Good question thanks.
...
May 17, 2016 at 12:24 am
Please explain why comparing the value 1 with the value 1 fails? The "explanation" talks about trailing zeroes, but the difference in the initialisation values are leading zeroes, which in any integer expression are irrelevant, surely?
May 17, 2016 at 2:04 am
Bob JH Cullen (5/17/2016)
which in any integer expression are irrelevant, surely?
This is not an integer expression.
The code first checks the binary values for @a and @b-2, do they match, well yes they do.
You now enter the nested IF, do @C and @d match?
No they don't because binary values 0x01 and 0x0001 are clearly different
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 17, 2016 at 2:26 am
Perry Whittle (5/17/2016)
Bob JH Cullen (5/17/2016)
which in any integer expression are irrelevant, surely?This is not an integer expression.
The code first checks the binary values for @a and @b-2, do they match, well yes they do.
You now enter the nested IF, do @C and @d match?
No they don't because binary values 0x01 and 0x0001 are clearly different
Perry, I can't follow that logic. If you are saying that, for a binary comparison, the number of bits included in the expression is relevant, then 0x00 is clearly not the same as 0x0000, the way it is written. If they evaluate to be equal, then there is an assumption about *value* as opposed to its textual representation. For 0x01 and 0x0001 to NOT be equal, it implies that the bits are left-justified in their evaluation, as the decimal values 0.01 and 0.0001 are. There is nothing about a binary number that implies a fractional part, is there? So it must come down to alignment. And to my eyes, right-aligned numbers are logical. We assign powers of 2 to the bits in a binary number starting on the right.
May 17, 2016 at 2:40 am
This was removed by the editor as SPAM
May 17, 2016 at 2:49 am
It's very interesting, because that
DECLARE
@a VARBINARY(20) = 0x10
, @b-2 VARBINARY(20) = 0x1000
, @C VARBINARY(20) = 0x01
, @d VARBINARY(20) = 0x0001;
IF ( @C = @d )
PRINT 'Whole';
ELSE
PRINT 'Half';
ELSE
PRINT 'None'; returns Half as well.
I think, the comparison has nothing to do with the numeric value of the varbinary strings. But then why are 0x00 and 0x0000 equal?
May 17, 2016 at 2:50 am
Bob,
It comes down to how the OS stores data
0x01 is an 8 bit hex number - binary 00000001
0x0001 is a 16 bit hex number 0000000000000001
if you compare these as 16 bits you are comparing 0000000100000000 and 0000000000000001 which are clearly not the same.
With SQL going to be supported on Linux in the future this may become a lot more complicated as you may need to start considering whether you are running on a big endian or little endian OS.
May 17, 2016 at 3:00 am
palotaiarpad (5/17/2016)
It's very interesting, because that
DECLARE@a VARBINARY(20) = 0x10
, @b-2 VARBINARY(20) = 0x1000
, @C VARBINARY(20) = 0x01
, @d VARBINARY(20) = 0x0001;
IF ( @C = @d )
PRINT 'Whole';
ELSE
PRINT 'Half';
ELSE
PRINT 'None'; returns Half as well.
I think, the comparison has nothing to do with the numeric value of the varbinary strings. But then why are 0x00 and 0x0000 equal?
I think that clears things up for me.
I was thinking about the 0's to the left were significant, but it's actually the 0's on the right that cause this behaviour.
Great question!
May 17, 2016 at 3:32 am
crmitchell (5/17/2016)
With SQL going to be supported on Linux in the future this may become a lot more complicated as you may need to start considering whether you are running on a big endian or little endian OS.
I'm pretty sure the endian-ness of a system is determined by the CPU installed, not the OS; the Mac was big-endian once upon a time because it was running on the Motorola 68k, which was big-endian. Since SQL for Linux will presumably only run on x86, it should be little-endian just as it is on Windows.
May 17, 2016 at 3:46 am
Bob JH Cullen (5/17/2016)
then 0x00 is clearly not the same as 0x0000, the way it is written.
I see it to be a single nothing or a whole bunch of nothing = nothing
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 17, 2016 at 3:52 am
This was removed by the editor as SPAM
May 17, 2016 at 3:54 am
As usual, I am getting more confused the more I look into things! I use (rightly or wrongly - let's not get into that discussion) integer data as bitfields to store, for example, a set of flags against an object in the database. So I frequently use the & (bitwise-AND) operator to test for flags being set or not. And it matters not a jot if I use 0x1, 0x01 or 0x000001 to test the bottom bit. It all works as you would expect. So what does that tell us about the significance of the left-most zeroes in those bit masks? It says they are not relevant, is what!
So, just now, I did a test to see what would happen if i was testing against a binary value instead. I figured that maybe, as I was using binary values and bitwise-tests, I perhaps should have used binary rather than int in the first place. I don't think I have ever deployed a solution that used a varbinary data type, so this was a first for me. Imagine my surprise when I tried to run
declare @bitfield varbinary(32) = 0x01F9 -- arbitrary set of bits
if (@bitfield & 0x1) != 0
print 'Bit 0 is set'
if (@bitfield & 0x0001) != 0
print 'Bit 0 is still set!'
and SQL complained saying
Msg 402, Level 16, State 1, Line 2
The data types varbinary and varbinary are incompatible in the '&' operator.
What! You can't use a binary operator on two binary values?!!
So, T-SQL can only have one varbinary expression in such an operation, and the other must be integer. So that just reinforces my view that the left-most zeroes must be irrelevant in a hex value. Even back in the "good old days" when I played with bit-slice processors, machine code programming and so on, we always left-padded binary values of unequal lengths - a byte containing a 0-255 value must be the same as a long int containing the same values.
I'm retiring from this game soon - going back to building model railroads - far less hassle:-)
May 17, 2016 at 5:17 am
I thought it was a pretty straightforward question. Thanks.
May 17, 2016 at 6:25 am
Just guessed what seemed logical.
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply