从Hive迁移到BigQuery后执行查询报错：SUBSTR()第三个参数不能为负

报错原因 #

BigQuery的SUBSTR函数严格要求第三个参数（截取长度）必须是非负数，而你的查询里INSTR(variable, 'f') - INSTR(variable, 'a') - 3可能出现以下情况导致计算结果为负：

• 字符串中f的位置在a的位置+2以内（比如a后面紧跟f）
• 字符串中找不到a或f，导致INSTR返回0，计算后出现负数

而Hive的SUBSTR允许长度参数为负，此时会自动返回从起始位置到字符串末尾的内容，这是两者的核心差异。

解决方案 #

根据业务需求，有两种常见修改方式：

方式1：当长度为负时返回从起始位置到末尾的内容 #

用IF判断长度是否为正，正的话按原逻辑截取，负的话直接取从起始位置到末尾的内容：

IF(
  INSTR(variable, 'f') - INSTR(variable, 'a') - 3 > 0,
  SUBSTR(variable, INSTR(variable, 'a') + 2, INSTR(variable, 'f') - INSTR(variable, 'a') - 3),
  SUBSTR(variable, INSTR(variable, 'a') + 2)
)

方式2：提前过滤无效场景，避免无效截取 #

如果业务上要求只有当f在a+3位置之后才截取，否则返回空字符串（或自定义默认值）：

IF(
  INSTR(variable, 'a') = 0 OR INSTR(variable, 'f') <= INSTR(variable, 'a') + 2,
  '', -- 可替换为你需要的默认值
  SUBSTR(variable, INSTR(variable, 'a') + 2, INSTR(variable, 'f') - INSTR(variable, 'a') - 3)
)

补充说明 #

• BigQuery中SUBSTR(str, pos)是合法写法，直接返回从pos位置开始到字符串末尾的所有字符
• 建议额外处理INSTR(variable, 'a') = 0的情况（即找不到'a'），避免起始位置为2导致的无效截取

内容的提问来源于stack exchange，提问作者Albol