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

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 5599 times
  1. Frank

    #1 by Frank - februari 10, 2010 at 19:46

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

    #2 by mehdi Saghari - februari 19, 2010 at 19:34

    Great solution
    Thanks :)
  3. Jason Rushton

    #3 by Jason Rushton - februari 25, 2010 at 19:30

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

    select ~mai_confirmed AS needsConfirmation
  4. Mehdi Saghari

    #4 by Mehdi Saghari - februari 25, 2010 at 20:09

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

    #5 by Paul Klinkenberg - februari 27, 2010 at 23:21

    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?
  6. phil

    #6 by phil - juni 30, 2010 at 15:20

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

    #7 by phil - juni 30, 2010 at 15:25

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

    #8 by Mehdi Saghari - juni 30, 2010 at 17:04

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

    #9 by Shahrooz - november 27, 2011 at 20:03

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

bawdy-bulbous