D_usv D_usv - 8 months ago 88
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

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