有趣而恼人的COALESCE函数

COALESCE是T-SQL中一个功能强大的函数，它的功能类似于ISNULL而等价于带一系列条件的CASE语句。然而由于SQL Server的隐式转换，稍不注意，可能会产生意想不到的结果。

先看第一组例子：

示例1

 

SELECT COALESCE(41,'abcde', '2010-1-1')

 

SELECT COALESCE(41,'abcde', CONVERT(DATETIME,'2010-1-1'))

 

输出结果：

 

-----------

41

 

(1 row(s) affected)

 

 

-----------------------

1900-02-11 00:00:00.000

 

(1 row(s) affected)

 

 

示例2

 

SELECT COALESCE(41,'abcde', '1234')

 

SELECT COALESCE(41,'abcde', 1234)

 

输出结果：

 

-----------

41

 

(1 row(s) affected)

 

 

-----------

41

 

(1 row(s) affected)

 

示例3：

 

SELECT COALESCE(0x41,'abcde', 1234)

 

SELECT COALESCE(0x41,'abcde', '1234')

 

 

输出结果：

 

-----------

65

 

(1 row(s) affected)

 

 

-----

A

 

(1 row(s) affected)

 

 

以下是COALESCE的连接帮助文档：

返回其参数中第一个非空表达式。

Transact-SQL 语法约定

语法

---

复制

 

COALESCE ( expression [ ,...n ] )

参数

---

expression

任何类型的表达式。

返回类型

---

返回数据类型优先级最高的 expression 的数据类型。如果所有表达式都不可为 Null，则结果的类型也不可为 Null。

注释

---

如果所有参数均为 NULL，则 COALESCE 返回 NULL。

注意：

至少应有一个 Null 值为 NULL 类型。

COALESCE(expression1,...n) 的功能与以下 CASE 表达式相同：

CASE

   WHEN (expression1 IS NOT NULL) THEN expression1

   WHEN (expression2 IS NOT NULL) THEN expression2

   ...

   ELSE expressionN

END

这里需要注意的是”返回数据类型优先级最高的 expression 的数据类型” 。

那么那个表达式的优先级最高呢，注意应该是expressionN而非expression1，

因此上便产生了上面一系列有趣的输出结果。

 

结论：使用COALESCE函数时一定要注意其中各项数据的数据类型，尽量避免SQL Server进行隐式转换，这样才能避免意想不到的输出结果。