Bigquery RegExp extract only numbers from string

Question

How do I extract only 1111111 from a product_name column like this in Bigquery:

AB~1111111|Omega | Shoes

I tried the following:

REGEXP_EXTRACT(product_name, r"^([0-9]+)|") AS Product_ID

Answer 1

You need to use

REGEXP_EXTRACT(product_name, r"^\D*(\d+)") AS Product_ID

See the regex demo

Details:

• ^ - string start
• \D* - zero or more non-digits
• (\d+) - Group 1: one or more digits.