8

I am trying to write a query that gets table information from a SQL CE database, ready to be put in c#, later to be exported to XML. I need one of the columns to be named 'IDENT' with a boolean value (to represent whether or not it is the identity column, obviously).

For this, I am checking if the AUTOINC_SEED column is null, as follows:

select isnull(AUTOINC_SEED) as IDENT from information_schema.columns

However, this returns TRUE for non-identity columns and FALSE for identity columns! Is there any way to reverse the boolean value inside the select statement?

Edit: I'm aware I could do a case statement to solve this particular problem, but it got me curious about inverting boolean (bit) values in SQL.

Mike Baxter
  • 5,986
  • 14
  • 62
  • 110

1 Answers1

22

The carat (^) in SQL Server is the bitwise exclusive OR operator.

Since 1 ^ 1 equals 0, and 1 ^ 0 equals 1, you can just do:

SELECT (1 ^ [YourBitColumn]) as InverseBit

I don't have SQL CE handy either, but since SQL CE appears to have this, I believe the following query should do the trick:

select (1 ^ AUTOINC_SEED) as IDENT from information_schema.columns
LittleBobbyTables - Au Revoir
  • 30,184
  • 25
  • 98
  • 113
  • Sorry only just had a chance to try it. Yes the carat worked - although I did have to replace the 1 with a 0 like so `select (0 ^ AUTOINC_SEED) as IDENT`. It wasn't so much that I needed help with this, more that I was curious. It can, after all, be solved with a `case` statement - but thanks for answering the question and satisfying my curiousity! – Mike Baxter Mar 13 '13 at 09:36
  • looks like tilda does the trick (~) http://stackoverflow.com/a/177893/1579626 See also https://msdn.microsoft.com/en-us/library/ms173468.aspx – sǝɯɐſ Jun 14 '16 at 19:58