SYSK 131: SQL: String to Bit Conversion

  • Article

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

*/