Obtain everything from string after last underscore in every string

Question

I am trying to extract everything from my list of strings after the last underscore, after MOB_ in big query.

See example below:

STRING:

Topshop_AW19_Pro_MOB_competitors(mid price point)custom_affinity

DESIRED OUTPUT:

competitors(mid price point)custom_affinity

The last understand is in "customer_affinity", so I would expect "affinity" as the desired output. — Gordon Linoff, Nov 12 '19 at 16:02
So the everything after the last `_` and before `(` if it exists? — ctwheels, Nov 12 '19 at 16:07
Does `_(?:[^_]+\(.*|[^_]+)$` work for you? - just remove first character from result. — ctwheels, Nov 12 '19 at 16:12
STRING: LL CORPORATE_PRO_TRAFFIC_LAL 2% LLUK PIXEL USING PURCHASE_I810 — Suhayb Dar, Jan 04 '21 at 16:20
OUTPUT NEEDED: _I810 - ONLY ALLOW NUMBERS TO BE EXTRACTED AFTER _I810 — Suhayb Dar, Jan 04 '21 at 16:21

Gosfly · Accepted Answer · 2019-11-13T11:24:01.473

0

Here is one of the possible solutions :

SELECT SUBSTRING(your_field, LOCATE('MOB_',your_field)+4)
FROM your_table;

or with regexp

SELECT REGEXP_SUBSTR(your_field,'(?<=MOB_).*$') 
FROM your_table

EDIT with oracle :

SELECT substr(regexp_substr(your_field,'MOB_(.*)'),5) "result" FROM your_table;

if you don't want to use substr http://sqlfiddle.com/#!4/a4021/20 :

SELECT regexp_substr(your_field,'MOB_(.*)', 1, 1, NULL, 1) "result" FROM your_table;

Edit 2 with oracle : See more examples here http://sqlfiddle.com/#!4/a4021/31 One with regexp_replace and one with instr (= find in oracle).

edited Nov 13 '19 at 11:24

answered Nov 12 '19 at 16:12

Gosfly

I don't see LOCATE in the documentation for bigquery -- that is oracle and db2 right? – Hogan Nov 12 '19 at 16:19
1

ok -- also FIND https://cloud.google.com/dataprep/docs/html/FIND-Function_57344729 – Hogan Nov 12 '19 at 18:43
How would i do this with the find function? – Suhayb Dar Nov 13 '19 at 11:03
In oracle I would use instr function which is like locate and I suppose find ? See my second edit above with some more examples – Gosfly Nov 13 '19 at 11:24

score 0 · Answer 2 · answered Nov 12 '19 at 16:16

This seems direct to the point:

RIGHT(stuff, RIGHT_FIND(stuff, 'MOB_'))

Interesting the documentation seems to have some errors:

2 Answers2