sql server 2005 opposite value function

bbraman used Ask the Experts™
I'm not sure what to call this, but I need a SQL Server function that will return the opposite of the value of a column, so that if the column contains a 1 a 0 is returned, and if it contains a 0 a 1 is returned.  If it contains anything other then a 1 or a 0 then the original value is returned unaltered.

I would suspect something like this would be built into SQL Server 2005, but perhaps a custom function is required.

Thanks for considering my request.
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
an example using adventure works and CASE
select DaysToManufacture, 
  case DaysToManufacture
       when 0 then 1
       when 1 then 0
       else DaysToManufacture
  end as oppossite, * from Production.Product

Open in new window

And of course you can put this code into a function
CREATE FUNCTION dbo.Opposite (@Value int)
declare @Result int
  select  @Result  = Case @value
       when 0 then 1
       when 1 then 0
       else @Value
Return @Result

And then you can just use the function to get the same results as above:
select dbo.Opposite(DaysToManufacture), *
  from Production.Product

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial