[Solved] Bigquery REGEX getting numbers only that followed by certain text (unit)

Question

We can use REGEXP_EXTRACT here with a capture group:

SELECT product, REGEXP_EXTRACT(product, r'([0-9]+)(?:l|ml|gr|g|mg|[a-z]+s)\b') AS Extracted
FROM yourTable;

Accepted Answer

We can use REGEXP_EXTRACT here with a capture group:

SELECT product, REGEXP_EXTRACT(product, r'([0-9]+)(?:l|ml|gr|g|mg|[a-z]+s)\b') AS Extracted
FROM yourTable;