BigQuery; extract numbers only from a string

Question

my data looks like a 1x1000 vector with variable number of inputs in a like. sometimes it is just age but sometimes they add weight and state ID.

85 age
15 age; 68 Weight
25 age; 80 Weight; 02 Alaska
72 Weight; 50 Wyoming

What I would like to get as output are just numbers - i.e.

85
15 68 
25 80 02 
72 50

I don't succeed that much using SPLIT since that gives me over 2000 lines back instead of 1000. So I don't know how to do it. Unless SPLIT could be combined and tell me how many information points were there before split. I.e.

85    1
15    2
68    2
25    3
80    3

etc.

Answer 1

You can use REGEXP_REPLACE:

SELECT REGEXP_REPLACE("25 age; 80 Weight; 02 Alaska",'[^0-9 ]','')

Read more about Regular Expression functions