为什么T-SQL ISNULL()截断字符串，而合并不是?

Given the following:

鉴于以下几点:

SELECT ISNULL('XY' + NULL, 'ABCDEFGHIJ') -- Outputs ABC (Why?)
SELECT COALESCE('XY' + NULL, 'ABCDEFGHIJ') -- Outputs ABCDEFGHIJ

Why are these statements returning different results?

为什么这些语句返回不同的结果?

3 个解决方案

#1

According to Microsoft documentation, for function:

根据Microsoft文档，功能:

ISNULL(check_expression, replacement_value)

replacement_value must be of a type that is implicitly convertible to the type of check_expression. Note that type for 'xy'+NULL is VARCHAR(3). Because of this your string 'ABCDEFGHIJ' is cast to VARCHAR(3) and thus trimmed.

replacement_value必须具有隐式转换为check_expression类型的类型的类型。注意，“xy”+NULL的类型是VARCHAR(3)。因此，你的字符串“ABCDEFGHIJ”被转换为VARCHAR(3)并因此被削减。

It sounds strange why it is not VARCHAR(2), but this is the way it is - one character longer than 'xy'. You can play with this SQLFiddle and see for yourself that type for 'xy'+NULL is the same as for expression CASE WHEN 1=2 THEN 'XYZ' ELSE NULL END, which is NULL but is implicitly compatible to VARCHAR(3).

听起来很奇怪为什么它不是VARCHAR(2)，但它就是这样——一个比xy长一点的字符。您可以使用这个SQLFiddle，并自己查看‘xy’+NULL的类型与表达式的情况相同，当1=2时，‘XYZ’ELSE NULL结尾，它是NULL，但隐式兼容VARCHAR(3)。

It seems that for expression 'xy'+NULL perceived length can be computed as 'xy' string length (2) plus 1 for every NULL added. For example, type of 'xy'+NULL+NULL is VARCHAR(4), type for 'xy'+NULL+NULL+NULL is VARCHAR(5) and so on - check out this SQLFiddle. This is extremely weird, but that is how MS SQL Server 2008 and 2012 work.

对于表达式'xy'+空感知长度，似乎可以计算为'xy'字符串长度(2)+ 1。例如，“xy”+NULL+NULL的类型是VARCHAR(4)，“xy”+NULL+NULL+NULL +NULL的类型是VARCHAR(5)，等等——看看这个SQLFiddle。这非常奇怪，但这就是SQL Server 2008和2012的工作方式。

#2

You can check all the difference here, its very clear

你可以在这里检查所有的差异，很明显

MSDN : http://msdn.microsoft.com/en-us/library/ms190349.aspx

MSDN:http://msdn.microsoft.com/en-us/library/ms190349.aspx

MSDN Blog : http://blogs.msdn.com/b/sqltips/archive/2008/06/26/differences-between-isnull-and-coalesce.aspx

MSDN博客:http://blogs.msdn.com/b/sqltips/archive/2008/06/26/differences-between-isnull-and-coalesce.aspx

#3

ISNULL() converts the replacement value to the type of the check expression. In this case, the type of the check expression is CHAR(2), so converting the replacement value truncates it (are you sure you're getting ABC and not just AB?).

ISNULL()将替换值转换为检查表达式的类型。在这种情况下，检查表达式的类型是CHAR(2)，因此转换替换值会截断它(您确定您得到的是ABC而不是AB吗?)

From the Microsoft documentation:

从微软文档:

replacement_value can be truncated if replacement_value is longer than check_expression.

若replacement_value比check_expression长，可以截断replacement_value。

#1