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

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

• sql
• google-bigquery

• Not an exact duplicate, but I think this should help: stackoverflow/q/27060396/2422776 – Mureinik Commented Jan 30 at 8:42
• Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking. – Community Bot Commented Jan 30 at 23:01
• Thanks @Mureinik - the offset did the trick. – JohnnyS4322 Commented Feb 11 at 1:45

1 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;