How can I do Instr() function in Standard SQL (Non-Legacy) on Big Query

Question

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

Answer 1

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