SQL 如何使用case语句捕获NULL值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2085100/
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 catch NULL values using case statement
提问by rahul
here in this query I want to replace the values in Person.Contact of Adventureworks database with some new values. The below query case statement is working fine for other values but I am not able to change the values those are in NULL. I am using SQL Server. Any help is appreciated.
在此查询中，我想用一些新值替换 Adventureworks 数据库的 Person.Contact 中的值。下面的查询 case 语句适用于其他值，但我无法更改那些为 NULL 的值。我正在使用 SQL Server。任何帮助表示赞赏。
select contactid,Title,FirstName,MiddleName, case MiddleName when 'R.' then 'Robert' when 'B.' then 'Bids' when 'J.' then 'John' when is null then 'New Name' else 'No Name' end, LastName from Person.Contact
回答by GSerg
case
when MiddleName is null then ...
when MiddleName = 'R' then ...
end
回答by marc_s
I'd use the ISNULL function - it will return the value given if the field is NULL:
我会使用 ISNULL 函数 - 如果字段为 NULL，它将返回给定的值：
select contactid,Title,FirstName,MiddleName,
case ISNULL(MiddleName, 'NULLVALUE')
when 'R.' then 'Robert'
when 'B.' then 'Bids'
when 'J.' then 'John'
when 'NULLVALUE' then 'New Name'
else 'No Name'
end, LastName from Person.Contact
回答by eromrab
Sorry to post 7 years later, but I've been trying to find a solution for Interbase / Firebird and this post kept popping up. None of the solutions here work because there is no ISNULL, so I figured I'd help anyone else who might come here looking for that:
很抱歉在 7 年后发帖，但我一直在尝试为 Interbase / Firebird 寻找解决方案，而这篇帖子不断弹出。这里的解决方案都不起作用，因为没有 ISNULL，所以我想我会帮助任何可能来这里寻找的人：
select contactid,Title,FirstName,MiddleName,
case COALESCE(MiddleName, 'NULLVALUE')
when 'R.' then 'Robert'
when 'B.' then 'Bids'
when 'J.' then 'John'
when 'NULLVALUE' then 'New Name'
else 'No Name'
end, LastName from Person.Contact
