如何控制在 sql server 中将 null int 字段转换为 varchar？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7557588/
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 control casting of null int field to varchar in sql server?
提问by TheBoyan
First of all I would like to know how does CASTwork with NULLfields and how does it behave when the value is NULL?
首先，我想知道如何CAST处理NULL字段以及当值为 时它的行为如何NULL？
For example in the expression:
例如在表达式中：
(CAST(INT_FIELD as nvarchar(100))
what happens if the value INT_FIELDis NULL?
如果该值时会发生什么INT_FIELD是NULL？
The reason is that when I'm trying to do the following:
原因是当我尝试执行以下操作时：
SELECT (CAST(INT_FIELD as nvarchar(100)) + ' ' + SOME_OTHER_FIELD FROM SOME_TABLE;
I'm getting NULLeven though the SOME_OTHER_FIELDis not null. I'm guessing it has some kind of logic that NULL + something = NULLbut I'm not sure.
NULL即使SOME_OTHER_FIELD不为空，我也得到了。我猜它有某种逻辑，NULL + something = NULL但我不确定。
How can I control this behavior?
我怎样才能控制这种行为？
回答by Lamak
You need to use ISNULLor COALESCE, since most row operation between a NULLis gonna result in NULL. CASTof a NULLreturns NULLand NULL+ something is also NULL. In your example you should do something like this:
您需要使用ISNULL或COALESCE，因为 a 之间的大多数行操作NULL都会导致NULL. CASTA的NULL收益NULL和NULL+东西也是NULL。在您的示例中，您应该执行以下操作：
SELECT ISNULL(CAST(INT_FIELD as nvarchar(100)),'') + ' ' + ISNULL(SOME_OTHER_FIELD,'')
FROM SOME_TABLE;
Of course, in my example, if both fields are NULLit will return ' ' instead of '', but you get the idea.
当然，在我的示例中，如果两个字段都存在NULL，它将返回 ' ' 而不是 ''，但您明白了。
回答by downforce
Look into COALESCE, where you can find the first non-null and return 0 if all are null, e.g:
查看 COALESCE，您可以在其中找到第一个非空值，如果全部为空，则返回 0，例如：
SELECT (CAST(COALESCE(INT_FIELD,0) as nvarchar(100)) + ' ' + SOME_OTHER_FIELD FROM SOME_TABLE;
回答by Vinay
Try using COALESCE
尝试使用 COALESCE
SELECT COALESCE(CAST(INT_FIELD as nvarchar(100), '') + ' ' + SOME_OTHER_FIELD FROM SOME_TABLE;
回答by a1ex07
Normally, NULL+(-,/,*, etc) something= NULL.
You can use
通常，NULL+(-,/,*, etc) something= NULL。您可以使用
SELECT ISNULL(CAST(INT_FIELD as nvarchar(100)),'')
+ ' ' + ISNULL(SOME_OTHER_FIELD FROM SOME_TABLE,'')
or you can SET CONCAT_NULL_YIELDS_NULL OFF(more details)
或者你可以SET CONCAT_NULL_YIELDS_NULL OFF（更多细节）
