blocks|key|987077|text|CASE+WHEN+last_name+IS+NULL+THEN+''+ELSE+'+'%2Blast_name+END|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|987078|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

blocks|key|987143|text|WHEN部分与==进行了比较，但不能真正与NULL进行比较。试一试|type|unstyled|depth|inlineStyleRanges|entityRanges|data|987144|CASE+WHEN+last_name+is+NULL++THEN+...+ELSE+..+END|code-block|syntax|javascript|987145|相反，或者合并：|987146|COALESCE('+'%2Blast_name,'')|987147|(‘’当last_name为NULL时，%2Blast_name为NULL，因此在这种情况下应返回‘’)|987148|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|S|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

The WHEN part is compared with ==, but you can't really compare with NULL. Try

<pre><code>CASE WHEN last_name is NULL THEN ... ELSE .. END
</code></pre>

instead or COALESCE:

<pre><code>COALESCE(' '+last_name,'')
</code></pre>

(' '+last_name is NULL when last_name is NULL, so it should return '' in that case)

blocks|key|987355|text|有很多解决方案，但没有一个解决方案涵盖了原始语句不起作用的原因。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|987356|CASE+last_name+WHEN+null+THEN+''+ELSE+'+'%2Blast_name|code-block|syntax|javascript|987357|在when之后，会检查是否相等，这应该是真或假。|987358|如果比较的一个部分或两个部分为空，则比较的结果将是未知的，这在case结构中被视为false。请参阅：https://www.xaprb.com/blog/2006/05/18/why-null-never-compares-false-to-anything-in-sql/|offset|length|987359|为了避免这种情况，Coalesce是最好的方法。|987360|entityMap|0|LINK|mutability|MUTABLE|url|https://www.xaprb.com/blog/2006/05/18/why-null-never-compares-false-to-anything-in-sql/^0|0|0|0|1F|2F|0|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|X|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Y|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|Z|8|@]|9|@$K|10|L|11|1|12]]|A|$]]|$1|M|3|N|5|6|7|13|8|@]|9|@]|A|$]]|$1|O|3|-4|5|6|7|14|8|@]|9|@]|A|$]]]|P|$Q|$5|R|S|T|A|$U|V]]]]

There are plenty of solutions but none covers why the original statement doesn't work.

<pre><code>CASE last_name WHEN null THEN '' ELSE ' '+last_name
</code></pre>

After the when, there is a check for equality, which should be true or false.

If one or both parts of a comparison is null, the result of the comparison will be UNKNOWN, which is treated like false in a case structure. See: <a href="https://www.xaprb.com/blog/2006/05/18/why-null-never-compares-false-to-anything-in-sql/" rel="noreferrer">https://www.xaprb.com/blog/2006/05/18/why-null-never-compares-false-to-anything-in-sql/</a>

To avoid this, Coalesce is the best way.

blocks|key|1201690|text|根据您的查询，您还可以执行以下操作：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1201691|SELECT+first_name+%2B+'+'+%2B+ISNULL(last_name,+'')+AS+Name+FROM+dbo.person|code-block|syntax|javascript|1201692|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Given your query you can also do this:

<pre><code>SELECT first_name + ' ' + ISNULL(last_name, '') AS Name FROM dbo.person
</code></pre>

blocks|key|987239|text|问题是null不被认为等于它自己，因此子句永远不会匹配。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|987240|您需要显式检查是否为null：|987241|SELECT+CASE+WHEN+last_name+is+NULL+THEN+first_name+ELSE+first_name+%2B+'+'+%2B+last_name|code-block|syntax|javascript|987242|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|L|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|M|8|@]|9|@]|A|$G|H]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

The problem is that null is not considered equal to itself, hence the clause never matches.

You need to check for null explicitly:

<pre><code>SELECT CASE WHEN last_name is NULL THEN first_name ELSE first_name + ' ' + last_name
</code></pre>

blocks|key|987297|text|尝试：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|987298|SELECT+first_name+%2B+ISNULL('+'%2Blast_name,+'')+AS+Name+FROM+dbo.person|code-block|syntax|javascript|987299|这会将空格添加到姓氏，如果为null，则整个space%2Blast名称为NULL，并且您只获得名字，否则您将获得firts%2Bspace%2Blast名称。|987300|只要设置了使用空字符串连接的默认设置，这就会起作用：|987301|SET+CONCAT_NULL_YIELDS_NULL+ON+|987302|这不应该是一个问题，因为在SQl服务器的未来版本中OFF模式将会消失|offset|length|style|CODE|987303|entityMap^0|0|0|0|0|0|P|3|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|W|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|X|8|@]|9|@]|A|$]]|$1|K|3|L|5|D|7|Y|8|@]|9|@]|A|$E|F]]|$1|M|3|N|5|6|7|Z|8|@$O|10|P|11|Q|R]]|9|@]|A|$]]|$1|S|3|-4|5|6|7|12|8|@]|9|@]|A|$]]]|T|$]]

try:

<pre><code>SELECT first_name + ISNULL(' '+last_name, '') AS Name FROM dbo.person
</code></pre>

This adds the space to the last name, if it is null, the entire space+last name goes to NULL and you only get a first name, otherwise you get a firts+space+last name.

this will work as long as the default setting for concatenation with null strings is set:

<pre><code>SET CONCAT_NULL_YIELDS_NULL ON 
</code></pre>

this shouldn't be a concern since the <code>OFF</code> mode is going away in future versions of SQl Server

blocks|key|1202117|text|找到了解决这个问题的办法。只需对CASE语句执行ISNULL命令：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1202118|ISNULL(CASE+x+WHEN+x+THEN+x+ELSE+x+END,+'')+AS+'BLAH'|code-block|syntax|javascript|1202119|entityMap^0|G|4|O|6|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]|$9|P|A|Q|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|R|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|S|8|@]|D|@]|E|$]]]|L|$]]

Found a solution to this. Just <code>ISNULL</code> the <code>CASE</code> statement:
<pre><code>ISNULL(CASE x WHEN x THEN x ELSE x END, '') AS 'BLAH'
</code></pre>

blocks|key|987376|text|CASE++
++++WHEN+last_name+IS+null+THEN+''+
++++ELSE+'+'+%2B+last_name+
END|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|987377|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

blocks|key|1201848|text|杰森抓到了一个错误，所以这是有效的.|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1201849|有没有人能确认其他平台的版本？|1201850|SQL+Server：|1201851|SELECT
CASE+LEN(ISNULL(last_name,''))+
WHEN+0+THEN+''+
ELSE+'+'+%2B+last_name
END+AS+newlastName|code-block|syntax|javascript|1201852|MySQL：|1201853|SELECT
CASE+LENGTH(IFNULL(last_name,''))+
WHEN+0+THEN+''+
ELSE+'+'+%2B+last_name
END+AS+newlastName|1201854|Oracle：|1201855|SELECT
CASE+LENGTH(NVL(last_name,''))+
WHEN+0+THEN+''+
ELSE+'+'+%2B+last_name
END+AS+newlastName|1201856|entityMap^0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|V|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|W|8|@]|9|@]|A|$]]|$1|F|3|G|5|H|7|X|8|@]|9|@]|A|$I|J]]|$1|K|3|L|5|6|7|Y|8|@]|9|@]|A|$]]|$1|M|3|N|5|H|7|Z|8|@]|9|@]|A|$I|J]]|$1|O|3|P|5|6|7|10|8|@]|9|@]|A|$]]|$1|Q|3|R|5|H|7|11|8|@]|9|@]|A|$I|J]]|$1|S|3|-4|5|6|7|12|8|@]|9|@]|A|$]]]|T|$]]

Jason caught an error, so this works... 

Can anyone confirm the other platform versions? 
SQL Server:

<pre><code>SELECT
CASE LEN(ISNULL(last_name,'')) 
WHEN 0 THEN '' 
ELSE ' ' + last_name
END AS newlastName
</code></pre>

MySQL:

<pre><code>SELECT
CASE LENGTH(IFNULL(last_name,'')) 
WHEN 0 THEN '' 
ELSE ' ' + last_name
END AS newlastName
</code></pre>

Oracle:

<pre><code>SELECT
CASE LENGTH(NVL(last_name,'')) 
WHEN 0 THEN '' 
ELSE ' ' + last_name
END AS newlastName
</code></pre>

blocks|key|1201890|text|您可以使用IsNull函数|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1201891|select+
++++isnull(rtrim(ltrim([FirstName]))%2B'+','')+%2B
++++isnull(rtrim(ltrim([SecondName]))%2B'+','')+%2B
++++isnull(rtrim(ltrim([Surname]))%2B'+','')+%2B
++++isnull(rtrim(ltrim([SecondSurname])),'')
from+TableDat|code-block|syntax|javascript|1201892|如果有一列为null，则会得到一个空字符|1201893|与Microsoft+SQL+Server+2008%2B兼容|1201894|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|P|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

You can use IsNull function

<pre><code>select 
 isnull(rtrim(ltrim([FirstName]))+' ','') +
 isnull(rtrim(ltrim([SecondName]))+' ','') +
 isnull(rtrim(ltrim([Surname]))+' ','') +
 isnull(rtrim(ltrim([SecondSurname])),'')
from TableDat
</code></pre>

if one column is null you would get an empty char

Compatible with Microsoft SQL Server 2008+

blocks|key|1201944|text|我尝试转换为字符串并测试长度为零的字符串，它起作用了。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1201945|CASE+
+++WHEN+LEN(CAST(field_value+AS+VARCHAR(MAX)))+=+0+THEN+
+++++++DO+THIS
END+AS+field+|code-block|syntax|javascript|1201946|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

I tried casting to a string and testing for a zero-length string and it worked.

<pre><code>CASE 
 WHEN LEN(CAST(field_value AS VARCHAR(MAX))) = 0 THEN 
 DO THIS
END AS field 
</code></pre>

blocks|key|1201996|text|使用SQL+Server+2012及更高版本中提供的CONCAT功能。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1201997|SELECT+CONCAT([FirstName],+'+,+'+,+[LastName])+FROM+YOURTABLE|code-block|syntax|javascript|1201998|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Use the CONCAT function available in SQL Server 2012 onward.

<pre><code>SELECT CONCAT([FirstName], ' , ' , [LastName]) FROM YOURTABLE
</code></pre>

blocks|key|1202075|text|NULL不等于任何内容。case语句基本上是指当值=+NULL时。它永远不会命中。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1202076|还有几个系统存储过程的语法写得不正确。参见sp_addpullsubscription_agent和sp_who2。|1202077|我希望我知道如何通知微软这些错误，因为我不能改变系统存储的procs。|1202078|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

NULL does not equal anything. The case statement is basically saying when the value = NULL .. it will never hit. 
There are also several system stored procedures that are written incorrectly with your syntax. See sp_addpullsubscription_agent and sp_who2. 
Wish I knew how to notify Microsoft of those mistakes as I'm not able to change the system stored procs.

blocks|key|1202182|text|在SQL+Server+2017中，Microsoft引入了Concatenate+With+Separator函数，仅针对您的情况：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1202183|SELECT+CONCAT_WS('+',+first_name,+last_name)+FROM+dbo.person|code-block|syntax|javascript|1202184|CONCAT_WS跳过空值，但不跳过空字符串。|1202185|有趣的是，MySQL在十多年前引入了CONCAT_WS。|1202186|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|P|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

In SQL Server 2017, Microsoft introduced a Concatenate With Separator function, for just your situation:
<pre><code>SELECT CONCAT_WS(' ', first_name, last_name) FROM dbo.person
</code></pre>
CONCAT_WS skips NULL values, but not empty strings.
Interestingly, MySQL introduced CONCAT_WS over a decade earlier.

I wrote a T-SQL Statement similar like this (the original one looks different but I want to give an easy example here):

<pre><code>SELECT first_name + 
 CASE last_name WHEN null THEN 'Max' ELSE 'Peter' END AS Name
FROM dbo.person
</code></pre>

This Statement does not have any syntax errors but the case-clause always chooses the ELSE-part - also if the last_name is null. But Why?

What I want to do is to unite first_name and last_name, but if last_name is null the whole name becomes null:

<pre><code>SELECT first_name +
 CASE last_name WHEN null THEN '' ELSE ' ' + last_name END AS Name 
FROM dbo.person
</code></pre>

Do you know where the problem is?

T-SQL CASE Clause: How to specify WHEN NULL

SQL Server

服务器

MySQL

我写了一个类似这样的T-SQL语句(原始语句看起来不同，但我想在这里给出一个简单的例子)：SELECT first_name +     CASE last_name WHEN null THEN 'Max' ELSE 'Peter' END AS NameFROM dbo.person该语句没有任何语法错误，但如果last_name为null，则case子句始终选择ELSE-part。但是为什么

问T-SQL CASE子句:如何指定WHEN NULL
EN

回答 14

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问T-SQL CASE子句:如何指定WHEN NULLEN

回答 14

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问T-SQL CASE子句:如何指定WHEN NULL
EN