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.

In that last example ie

01000100 = 68

invert the bits (apply a ~)

10111011

when adding 1 shouldn’t the final answer be

10111100 = -68

?

LikeLike

You’re dead right there 🙂 I’ve go no idea how I came up with my answer 🙂

LikeLike