63

I'm converting some data in SQL Server:

INSERT INTO MYTABLE (AllowEdit)
(Select PreventEdit from SOURCETABLE)

so I need to inverse the bit value from source table. I expected NOT to work, as this is how I would do it in code, but it doesn't. The most elegant way I can think of is:

INSERT INTO MYTABLE (AllowEdit)
(Select ABS(PreventEdit -1) from SOURCETABLE)

Is there a more standard way to do it?

gbn
  • 422,506
  • 82
  • 585
  • 676
Molloch
  • 2,261
  • 4
  • 29
  • 48

3 Answers3

141

I did not test this myself, but you should be able to use the bitwise negation operator, ~ on a bit:

INSERT INTO MYTABLE (AllowEdit) 
(SELECT ~PreventEdit FROM SourceTable)
driis
  • 161,458
  • 45
  • 265
  • 341
32

NOT or XOR if bit

SELECT ~PreventEdit FROM SourceTable
SELECT 1 ^ PreventEdit FROM SourceTable

If it isn't actually bit in SourceTable then this:

SELECT 1 - PreventEdit FROM SourceTable

Edit: A test, note NOT is 2s complement so could give odd results if not used on a bit column

DECLARE @bitvalue bit = 1, @intvalue int = 1;

SELECT ~@bitvalue, ~@intvalue
SELECT 1 ^ @bitvalue, 1 ^ @intvalue
SELECT 1 - @bitvalue, 1 - @intvalue

SELECT @bitvalue = 0, @intvalue = 0

SELECT ~@bitvalue, ~@intvalue
SELECT 1 ^ @bitvalue, 1 ^ @intvalue
SELECT 1 - @bitvalue, 1 - @intvalue
gbn
  • 422,506
  • 82
  • 585
  • 676
  • Brilliant. Thanks a lot, I couldn't find this anywhere but it was probably my search terms. – Molloch Jul 24 '10 at 13:07
  • In my case, XOR proved the useful approach. I had a table where a column was labeled 'IsDeleted', but was an int, not a bit. I was trying to convert it to IsActive as a bit. Simply doing ~IsDeleted created a value of -2 (*definitely* not right). So, I selected on `1 ^ IsDeleted` to get the correct IsActive value (1 ^ 1 = bit 0, 1 ^ 0 = bit 1). – Andrew Gray Apr 27 '18 at 16:16
0

INSERT INTO MYTABLE (AllowEdit) (SELECT ~ISNULL(PreventEdit,0) FROM SourceTable)

Red
  • 3,030
  • 3
  • 22
  • 39
Michel MARTIN
  • 101
  • 5