Extract part of string using BigQuery

Question

I have a text field with a directory structure of which I'd like to extract either the 2nd or 3rd word from the field - depending on other criteria. The structure is separated by '/' character. Example being:

Structure
Word 1/Word 2/Word 3/Word 4
Word 1/Word 2/Word 3

Output would be either "Word 2" or "Word 3" depending on other criteria.

I've tried with regexp_extract and regexp_replace but it is returning the word after the very last '/'.

Thank you

Answer 1

you can use split to pull back all the individual values based on your separator and then if you want a specific ordinal positioned value use the offset value. For example, the following brings back the third word if it exists

Without the offset, all values would be returned;