SYSK 131: SQL: String to Bit Conversion

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



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




/*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




Comments (0)

Skip to main content