Say you want to convert any of the following ‘1’, ‘-1’, ‘true’, ‘t’, ‘True’, ‘t’, ‘Yes’ to bit 1 and ‘0’, ‘f’, ‘false’, NULL to bit 0.  How would you do that in one SQL statement?

 

One way (special thanks to Bill Wendel who presented this solution) is this:

select case when charindex(left(@value, 1), 'tT1yY-') > 0 then 1 else 0 end

 

Used in a function it’ll like this:

 

Create Function dbo.fnIsTrue(@Value varchar(10))

            Returns bit

AS

            BEGIN

                         Return (Case WHEN CharIndex(left(@Value,1), 'tT1yY-') > 0 THEN 1 ELSE 0 END)

            END

GO

 

/*Example Calls:

 

  Select dbo.fnIsTrue('1')      --returns 1

  Select dbo.fnIsTrue('-1')     --returns 1

  Select dbo.fnIsTrue('true')   --returns 1

  Select dbo.fnIsTrue('t')      --returns 1

  Select dbo.fnIsTrue('0')      --returns 0

  Select dbo.fnIsTrue('f')      --returns 0

  Select dbo.fnIsTrue('false')  --returns 0

  Select dbo.fnIsTrue(NULL)     --returns 0

*/