在Select语句中替换NULL和空字符串

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.

我有一个列可以有NULL或空格（即''）值。我想用像'UNKNOWN'这样的有效值替换这两个值。我发现的各种解决方案建议修改表中的值。但是，在这种情况下，这不是一个选项，因为数据库适用于开发和/或修补非常差的第三方应用程序（实际上我认为我的罗威纳犬可以做得更好）。我担心修改基础数据可能会导致应用程序融入吸烟洞。

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

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

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

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.

当我说我已经找到解决我的具体问题并且没有找到答案时，你将不得不相信我。但是，如果我忽略了某些东西，请向我展示一条光明的道路。

3 个解决方案

#1

Try this

尝试这个

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

#2

Sounds like you want a view instead of altering actual table data.

听起来你想要一个视图，而不是改变实际的表数据。

Coalesce(NullIf(rtrim(Address.Country),''),'United States')

This will force your column to be null if it is actually an empty string (or blank string) and then the coalesce will have a null to work with.

如果它实际上是一个空字符串（或空字符串），那么这将强制您的列为null，然后coalesce将使用null。

#3

An alternative way can be this: - recommended as using just one expression -

另一种方法是： - 建议只使用一个表达式 -

case when address.country <> '' then address.country
else 'United States'
end as country

Note: Result of checking null by <> operator will return false.
And as documented: NULLIF is equivalent to a searched CASE expression
and COALESCE expression is a syntactic shortcut for the CASE expression.
So, combination of those are using two time of case expression.

注意：<>运算符检查null的结果将返回false。并且有记录：NULLIF等同于搜索的CASE表达式，COALESCE表达式是CASE表达式的语法快捷方式。因此，这些组合使用两次案例表达。

#1