Convert a Positive Number To a Negative In SQL With a Spot of Bitwise Logic

swans

Now this is something that I’ve seen asked a few times and it’s always a question that gets a fair number of different answers.  How do we go about converting a positive number to a negative, or the other way around?

I’ll give you my solution and it uses a little bit of bitwise logic.

The Short Answer

The short answer to the question is to simply apply a bitwise NOT to the number that you want to convert and add 1 to the result.  I wrote about bitwise logic in The Arcane Science of Bitwise Logic and SQL Server but didn’t mention the bitwise NOT.

If you want to apply a bitwise NOT to a value, simply use the ~

Give it a go, try converting 10 to a negative,


SELECT ~ 10 + 1

The result, -10.

You can also go the other way and convert a negative to a positive, give it a go with -9


SELECT ~ -9 + 1

…and the result, 9.

So that’s the short answer and that’s how to convert a positive to a negative and vice versa.

Carry on reading if you want to know why this works and exactly what’s happening.

The Long Answer

To really understand what’s going on, we need to understand what a biwise NOT actually does.

In nerdy talk, a bitwise NOT will return the ones’ complement of the value.  What that means to you and me is that all the bits in a particular value are inverted, a 1 will become a 0 and a 0 will become a 1.

Let’s take a look at the value 124, in binary that’s represented as 01111100.  Have a look at see what happens when we apply a bitwise NOT to that value.

01111100
~ 10000011

With the bits inverted, we get the number 131.

So how does this help us when we want to convert a positive number to a negative?

First of all you might want to remind yourself how binary works, I explained binary in Secret Codes And SQL Server, Part 1: Writing Our Own Encryption Algorithm and Cracking it.

Lets look at a few SQL datatypes for a moment,

Data type Max Value Storage
smallint 32767 2 Bytes
int 2147483648 4 Bytes
bigint 9223372036854775807 8 Bytes

Do you notice anything with those numbers?

Well, let’s take an int for example.  The max value of an int in SQL is 2,147,483,648 but it’s stored as a 4 byte datatype and the biggest number that we can represent in 4 bytes is 4,294,967,295.  What’s going on here?

Well all integer datatypes in SQL (except for tinyint) are signed datatypes, that’s what lets us represent both positive and negative numbers.  Because a value can be either positive or negative we need some way to tell them apart.

In binary, if we want to represent a negative number we use the twos’ complement value.  Twos’ complement simply means that we take the number, invert the bits and then add 1.

For example if we have a 1 byte signed datatype,

00000001 = 1
invert the bits,
11111110
add one
11111111 = -1

01000100 = 68
invert the bits (apply a ~)
10111011
add one
10111100 = -68

In this notation, the most significant bit, the one over on the left had side represents the signing of the value.  If that bit’s a 0 then we know that it’s going to be a positive number, but if it’s a 1 then the number will be negative.

So, going back to the original question, by applying the bitwise NOT (~) we’re flipping the bits and then adding one which will convert a positive to a negative or a negative to a positive.

 

3 thoughts on “Convert a Positive Number To a Negative In SQL With a Spot of Bitwise Logic

Add yours

  1. In that last example ie

    01000100 = 68
    invert the bits (apply a ~)
    10111011

    when adding 1 shouldn’t the final answer be

    10111100 = -68

    ?

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

Create a website or blog at WordPress.com

Up ↑

%d bloggers like this: