It appears I am unable to do Instr() in standard SQL on Big Query but have been unable to find an alternative function. Would really appreciate help with this.
example input:
John smith:hello
command:
SUBSTR(John smith:hello ,INSTR(John smith:hello ,
':')+1,LENGTH(John smith:hello))
required output:
hello
Yo can use STRPOS function for this
#standardSQL
SELECT SUBSTR('John smith:hello', STRPOS('John smith:hello', ':') + 1, LENGTH('John smith:hello'))
As an option - you can consider using REGEXP_EXTRACT function
#standardSQL
SELECT REGEXP_EXTRACT('John smith:hello', r':(.*)')
both give same output:
hello