SQL Question
How can I do Instr() function in Standard SQL (Non-Legacy) on Big Query
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
Recommended for you: Get network issues from WhatsUp Gold. Not end users.
Answer Source
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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download