Bigquery REGEX getting numbers only that followed by certain text (unit)-CodePudding

i have tables that contain product name. i want to extract the numbers only from it. but only numbers followed by the unit (certain text) ex: gr, kg, ml, pcs.

product name         | Extracted
milk 30ml            | 30
Cigarette 20pcs      | 20
Sugar 50gr           | 50
1990 chocolate 10gr  | 10

Is there any way to only getting number that followed certain text we desired? i just know how to extract numbers only but the last product will getting error. Thank you

CodePudding user response：

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;