在 Select 语句中替换 NULL 和空字符串 [英] Replacing NULL and empty string within Select statement

问题描述

我有一列可以有 NULL 或空白(即 '')值.我想用 'UNKNOWN' 这样的有效值替换这两个值.我发现的各种解决方案建议修改表本身中的值.但是，在这种情况下，这不是一个选项，因为数据库用于开发和/或修补得很差的 3rd 方应用程序(实际上我认为我的 Rottweiler 可以做得更好).我担心修改底层数据可能会导致应用程序陷入困境.

I have a column that can have either NULL or empty space (i.e. '') values. I would like to replace both of those values with a valid value like 'UNKNOWN'. The various solutions I have found suggest modifying the value within the table itself. However, this is not an option in this case in that the database is for a 3rd party application that is developed and/or patched very poorly (in reality I think my Rottweiler could have done a better job). I am concerned that modifying the underlying data could cause the application to melt into a smoking hole.

我尝试了以下命令的变体:

I have attempted variations of the following commands:

COALESCE(Address.COUNTRY, 'United States') -- Won't replace empty string as it is not NULL
REPLACE(Address.COUNTRY, '', 'United States') -- Doesn't replace empty string
ISNULL(Address.COUNTRY, 'United States') -- Works for NULL but not empty string

我知道我可以使用 CASE 语句，但我希望有一个更优雅/更有效的解决方案.

I know I could use the CASE statement but am hoping there is a much more elegant/efficient solution.

当我说我已经为我的特定问题寻找解决方案但没有找到答案时，您必须相信我.不过，如果我忽略了什么，请向我展示照亮的路径.

You'll have to trust me when I say I have looked for a solution to my specific issue and have not found an answer. If I have overlooked something, though, kindly show me the lighted path.

推荐答案

试试这个

COALESCE(NULLIF(Address.COUNTRY,''), 'United States')

这篇关于在 Select 语句中替换 NULL 和空字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！