Reverse boolean (bit) value in SQL CE select statement

Question

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.

Gregggreggory

Did you try my SQL Server Compact scripting API, can get all relevant schema information for you? – Permeable

@Permeable could you give me a link to it please? – Gregggreggory

You can find it at exportsqlce.codeplex.com – Permeable

Answer 1

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

Nazario

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! – Gregggreggory

looks like tilda does the trick (~) https://mcmap.net/q/237478/-boolean-39-not-39-in-t-sql-not-working-on-39-bit-39-datatype See also msdn.microsoft.com/en-us/library/ms173468.aspx – Galang

Answer 2

It may be more legible if you used the bitwise NOT operator (~):

SELECT  TBL.original_bit_column
       ,inverse_bit_column = ~ TBL.original_bit_column
FROM   your_table TBL

Smyth

wow ... in 20 years of this, I've never seen that! Love it, thank you! – Cruse