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


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


*/


 


 

Comments (0)

Skip to main content