How to convert varchar column to bit in SQL SERVER

Question

Flag1 - varchar column with true and false values. I need to convert this to a bit column.

When I try to do this:

 Convert(Bit,Flag1) 

error displayed

 Msg 245, Level 16, State 1, Line 2 Syntax error converting the varchar value 'False' to a column of data type bit. 

Answer 1

I suspect that the 'Flag1' field has other values ​​in addition to 'true' and 'false'. So check the values ​​in Flag1.

select the excellent Flag1 flag from YouTable.

Here is my proof:

 declare @Flag varchar(25) = 'False' select CONVERT(Bit, @Flag) 

It works great.

However, this will give the same error.

 declare @Flag varchar(25) = ' False' -- Pay attention to the the space in ' False'! select CONVERT(Bit, @Flag) 

-> Msg 245, level 16, state 1, line 2 Conversion error when converting varchar 'False' value to data type bit.

Pay attention to the place in the "False" field in the error message!

Answer 2

When choosing from a table, you can do this:

 SELECT CASE Flag1 WHEN 'true' THEN 1 ELSE 0 END AS FlagVal 

Syntax:

 CASE input_expression WHEN when_expression THEN result_expression [ ...n ] [ ELSE else_result_expression ] END Searched CASE expression: CASE WHEN Boolean_expression THEN result_expression [ ...n ] [ ELSE else_result_expression ] END