Extract repeating word from string

Extract Repeating Words from String

Description

If I wanted to extract all Workorder numbers from the string:

F1 - wo-12, some other text WOM-1235674,WOM-2345,WOM-3456

Where the workorder numbers always follow WOM-,
and I wanted to make sure each workorder number got it’s own row so I could join to another table, I could do:

SELECT 
   product_id, 
   wo_number
FROM 
   <TABLE>
CROSS JOIN 
   UNNEST(regexp_extract_all(wo_text,r'WOM-([1-9]+)')) as wo_number

where wo_text is the column of text that contains the workorder info.

Inspired by: https://www.reddit.com/r/bigquery/comments/oizgm6/extracting_word_from_string/

Example:

1 Like