How to convert a varchar column to bit column in SQL SERVER

Question

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

When I try to do this:

Convert(Bit,Flag1)

it shows an error

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

Banister

Just a reminder. Could you double-check your result to prevent false positives and negatives? Please run this: – Ruddle

declare @flag1 varchar(25) = ' True' -- leading blanks – Ruddle

select case @flag1 when 'true' then 1 when 'false' then 0 else 0 end – Ruddle

@JianHuang please don't answer in comment, also please review the existing answers. – Fabian

@DanAndrews sorry. still new in SO. Will move the answer. – Ruddle

@JianHuang Welcome to SO! It is a very friendly place to be, except the users are not friendly at all. :) – Fabian

@DanAndrews I have fun here. ^_^. However, I cannot delete my previous comments. – Ruddle

Answer 1

I suspect that there are other values in addition to 'true' and 'false' in the field 'Flag1'. So check for the values in Flag1.

select distinct Flag1 from YouTable.

Here is my proof:

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

It works fine.

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 failed when converting the varchar value ' False' to data type bit.

Pay attention to the the space in ' False' in the error message!

Ruddle

Answer 2

While selecting from 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

Uzzial

I needed to add the "END" keyword to the example. SELECT CASE Flag1 WHEN 'true' THEN 1 ELSE 0 END AS FlagVal. – Ultann

Answer 3

I do not think it is to do with if you have other values in your column. Its to do with how you've defined "true" or "false". SQL thinks it's a string rather than a bit. In your column I'd suggest using a Case Statement like:

select ...., case when ColumnName = "True" then 1 else 0 end as Flag1

Make sure you do not have any spaces in true or false. For that you could use:

rtrim(ltrim(ColumnName)) 

To remove any spaces.

Hyperpituitarism