BigQuery中空字符串拆分后ARRAY_LENGTH返回1如何解决

返回值异常是SPLIT函数的固有逻辑导致的：当传入的待分割字符串为空字符串时，SPLIT不会返回空数组，而是返回一个仅包含1个空字符串元素的数组，因此外层套ARRAY_LENGTH统计时会得到结果1，而非预期的0。

根据使用的SQL引擎支持的函数不同，可选择以下两种方案：

通用兼容写法（适配所有支持数组拆分的SQL引擎） #

在原有逻辑基础上增加空字符串判断，手动修正空串场景的统计值：

ARRAY_LENGTH(SPLIT(text_string, ",")) - IF(text_string = "", 1, 0)

逻辑说明：

• 当text_string为非空的正常逗号分隔字符串时，判断分支返回0，统计结果和原逻辑完全一致，给出的示例字符串会正常返回11
• 当text_string为空字符串时，判断分支返回1，用split得到的长度1减去1，得到预期的统计值0

简洁写法（支持NULLIF、COALESCE的引擎可用，如BigQuery、Spark SQL、PostgreSQL等） #

用NULLIF先把空字符串转为NULL，再做拆分和长度统计，最后用COALESCE把NULL结果转为0即可：

COALESCE(ARRAY_LENGTH(SPLIT(NULLIF(text_string, ""), ",")), 0)

注意：如果业务允许空字符串场景返回NULL而非0，可以去掉外层的COALESCE函数，直接写ARRAY_LENGTH(SPLIT(NULLIF(text_string, ""), ","))即可。

内容的提问来源于stack exchange，提问作者Cristian Avendaño