如何在 SQL SERVER 中将 varchar 列转换为位列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22382997/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to convert a varchar column to bit column in SQL SERVER
提问by user1254579
Flag1is a varcharcolumn with values "true" and "false". I need to convert this into bit column.
Flag1是varchar具有值“true”和“false”的列。我需要将其转换为位列。
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.
回答by Jian Huang
I suspect that there are other values in addition to 'true' and 'false' in the field 'Flag1'. So check for the values in Flag1.
我怀疑在“Flag1”字段中除了“true”和“false”之外还有其他值。因此，请检查 Flag1 中的值。
select distinct Flag1 from YouTable.
从 YouTable 中选择不同的 Flag1。
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.
-> 消息 245，级别 16，状态 1，行 2 将 varchar 值“False”转换为数据类型位时转换失败。
Pay attention to the the space in ' False' in the error message!
注意错误信息中'False'中的空格！
回答by Raging Bull
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
回答by Satista
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:
我认为这与您的专栏中是否有其他值无关。它与您如何定义“真”或“假”有关。SQL 认为它是一个字符串而不是一个位。在您的专栏中，我建议使用案例声明，例如：
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:
确保您在 true 或 false 中没有任何空格。为此，您可以使用：
rtrim(ltrim(ColumnName))
To remove any spaces.
删除任何空格。
