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 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.

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
11111111 = -1

01000100 = 68
invert the bits (apply a ~)
10111011
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”

1. LondonDBA says:

In that last example ie

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