需要将空白值替换为0。

Question

需要将空列替换为0，例如，表为

我尝试了这个查询来获得输出，但是它花费了更多的时间，因为我已经将这个表放到了过程中。

与使用“case”不同，我们是否可以将输出与任何其他概念一起使用？

select 
case when hail_n like '' then '0' else hail_n end as Hail_N, 
case when hail_s like '' then '0' else hail_s end as Hail_S,
case when hail_e like '' then '0' else hail_e end as Hail_E,
case when hail_w like '' then '0' else hail_w end as Hail_W,
case when wind_n like '' then '0' else wind_n end as Wind_N,
case when wind_s like '' then '0' else wind_s end as Wind_S,
case when wind_e like '' then '0' else wind_e end as Wind_E,
case when wind_w like '' then '0' else wind_w end as Wind_W
from vw_hail_wind_directions

谢谢

Answer 1

没有魔力；如果您有8列要输出，那么您需要有8个表达式来以任何方式更改输出。(你考虑过正常化吗？)

一种稍微不那么冗长的做法是：

SELECT 
  Hail_N = COALESCE(NULLIF(hail_n, ''),'0'),
  Hail_S = COALESCE(NULLIF(hail_s, ''),'0'),
  ...
FROM dbo.vw_hail_wind_directions; -- always use schema prefix and statement terminators