D_usv D_usv - 3 years ago 221
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


SUBSTR(John smith:hello ,INSTR(John smith:hello ,
':')+1,LENGTH(John smith:hello))

required output:


Answer Source

Yo can use STRPOS function for this

SELECT SUBSTR('John smith:hello', STRPOS('John smith:hello', ':') + 1, LENGTH('John smith:hello'))

As an option - you can consider using REGEXP_EXTRACT function

SELECT REGEXP_EXTRACT('John smith:hello', r':(.*)')

both give same output:

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download