Change SQL Server bit field to the opposite value: "NOT bit_fld"

Posted by Paul Klinkenberg in SQL Server on February 9, 2010

I had a DB column named 'mai_confirmed' (confirmed for mailing), but I needed a column with the value for 'needsConfirmation' for a mailing component.

Or, to say it codish: needsConfirmation = not mai_confirmed

I tried SELECT !mai_confirmed AS needsConfirmation, and also SELECT (NOT mai_confirmed) AS needsConfirmation, but those didn't work.

The trick is:

SELECT mai_confirmed + 1 % 2 AS needsConfirmation

Even though the resulting value is not a bit field anymore, it does return either NULL, 1, or 0. Which was fine enough for me ;-)

del.icio.us Digg StumbleUpon Facebook Technorati Fav reddit Google Bookmarks
| Viewed 7832 times
  1. Frank

    #1 by Frank - February 10, 2010 at 7:46 PM

    Following works too:
    SELECT abs( mai_confirmed - 1) AS needsConfirmation
    Reply
  2. mehdi Saghari

    #2 by mehdi Saghari - February 19, 2010 at 7:34 PM

    Great solution
    Thanks :)
    Reply
  3. Jason Rushton

    #3 by Jason Rushton - February 25, 2010 at 7:30 PM

    in sql server at least, you can flip a bit field by using the ~ (tilde) operator

    select ~mai_confirmed AS needsConfirmation
    Reply
  4. Mehdi Saghari

    #4 by Mehdi Saghari - February 25, 2010 at 8:09 PM

    it seams bit operator (OR XOR ...) are faster !?
    Reply
  5. Paul Klinkenberg

    #5 by Paul Klinkenberg - February 27, 2010 at 11:21 PM

    Great to see all the other solutions! I knew there had to be more, and even felt dumb about not knowing one by heart already.
    @Mehdi: can you give an example with the OR / XOR operator?
    Reply
  6. phil

    #6 by phil - June 30, 2010 at 3:20 PM

    Just use the carrot symbol for XOR. So "select bit_field ^ 1 from table" will give you the opposite value of what is stored in the field. Check this site for more info http://www.blackwasp.co.uk/SQLBitwiseOperators.aspx
    Reply
  7. phil

    #7 by phil - June 30, 2010 at 3:25 PM

    I forgot... the not operator is "~" . "select ~bit_field from table" ... this is slightly more elegant than exclusive or with 1 for bit values.
    Reply
  8. Mehdi Saghari

    #8 by Mehdi Saghari - June 30, 2010 at 5:04 PM

    yes, and as i said before i am sure it's faster and better ;-)
    Reply
  9. Shahrooz

    #9 by Shahrooz - November 27, 2011 at 8:03 PM

    thank you Mr frank for this solution
    Reply
(will not be published)
Leave this field empty

respective-eponymous