Third argument in SUBST() cannot be negative

Google Cloud Data Analytics
1

I went from hive to bigQuery. But when I run the query, I got this message : Third argument in SUBST() cannot be negative.

Subst(variable, instr(variable, ‘a’)+2, instr(variable, ‘f’) - instr(variable, ‘a’) - 3)

2

Do we mean the SUBSTR function? Looking here, it does indeed appear that the 3rd parameter must be >= 0.

Looking at SQL Server it seems it must be > 0.

Oracle seems to say that if the length < 1 then null is returned.

I’m not a hero in SQL but couldn’t we put a guard … eg.

SUBSTR(str, pos, IF(LEN>0, LEN, 0))