BigQuery:如何在字符串字段中值为空的情况下将默认值传递为x

Question

我试图硬编码一个值为x的列，当出现空值时，我已经尝试了下面的查询来检查，但我仍然看到列

中的空白。

**column - product_subcategory**
select 
ifnull(tbl.product_subcategory,'x') as product_subcategory
from dataset.layer_dim.tbl;

我不能硬编码为x，即使在测试了上面的查询我缺少什么?https://cloud.google.com/bigquery/docs/reference/standard-sql/conditional_expressions

Answer 1

您的查询是正确的，但是您可能会看到空字符串而不是空值。尝试使用NULLIF将空白值转换为NULL:

with sample as (
select '' as product_subcategory
union all
select null as product_subcategory
union all
select 'x' as product_subcategory
union all
select 'some value' as product_subcategory
)
select 
ifnull(nullif(product_subcategory, ''), 'x') as product_subcategory
from sample;

输出:

x
x
x
some value