处理Sql server字符串连接中的NULL

I have the following SQL query

我有以下SQL查询

select s.comments + s.further_comments from dbo.samples s where id = 1234

However if s.comments or s.further_comments is NULL the whole string is returned NULL

但是如果。注释或s。further - comments是NULL，整个字符串被返回为NULL

How do I convert the NULL value to an empty string or at least only return the non NULL values in this string?

如何将空值转换为空字符串，或者至少只返回该字符串中的非空值?

Thanks

谢谢

1 个解决方案

#1

You can use either ISNULL or COALESCE for this.

您可以为此使用ISNULL或联合。

SELECT ISNULL(s.comments, '') + ISNULL(s.further_comments, '')
SELECT COALESCE(s.comments, '') + COALESCE(s.further_comments, '')

ISNULL

Replaces NULL with the specified replacement value.

COALESCE

合并

Returns the first nonnull expression among its arguments.

Note that there are some differences between the two methods but for all intents and purposes, they most likely don't apply to your situation.

请注意，这两种方法之间存在一些差异，但实际上，它们很可能并不适用于您的情况。

ISNULL(NULL, NULL) -- is int

ISNULL(NULL, NULL)是int。

COALESCE(NULL, NULL) -- Will throw an error

联合(NULL, NULL)——将抛出一个错误

COALESCE(CAST(NULL as int), NULL) -- it valid and returns int

联合(CAST(NULL as int)， NULL)——它是有效的，返回int

ISNULL takes only 2 parameters whereas COALESCE takes variable number of parameters

ISNULL只接受两个参数，而COALESCE采用的参数数量是可变的

COALESCE is based on the ANSI SQL standard whereas ISNULL is a proprietary TSQL function

联合基于ANSI SQL标准，而ISNULL是一个专有的TSQL函数

#1