title: Extract numbers from string in Google BigQuery using regex
body: <p>I'm wondering if i can use regex in BigQuery to extract all the numbers from a string. </p>
  
  <p>I think the below works but just returns first hit - is there a way to extract all the hits.</p>
  
  <p>My use case here is that i basically want to get the biggest number from a url as that tends to be more like a post_id that i need to join on. </p>
  
  <p>here is an example of what i am talking about:</p>
  
  <pre><code>SELECT
    mystr,
    REGEXP_EXTRACT(mystr, r'(\d+)') AS nums
  FROM
    (SELECT 'this is a string with some 666 numbers 999 in it 333' AS mystr),
    (SELECT 'just one number 123 in this one ' AS mystr),
    (SELECT '99' AS mystr),
    (SELECT 'another -2 example 99' AS mystr),
    (SELECT 'another-8766 example 99' AS mystr),
    (SELECT 'http://somedomain.com/2015/12/this-is-a-post-with-id-in-url-99999' AS mystr),
    (SELECT 'http://somedomain.com/2015/12/this-is-a-post-with-id-in-url-99999/gallery/001' AS mystr),
    (SELECT 'http://somedomain.com/2015/12/this-is-a-post-with-id-in-url-99999/print-preview' AS mystr)
  </code></pre>
  
  <p>Results i get from this are:</p>
  
  <pre><code>[
    {
      "mystr": "this is a string with some 666 numbers 999 in it 333",
      "nums": "666"
    },
    {
      "mystr": "just one number 123 in this one ",
      "nums": "123"
    },
    {
      "mystr": "99",
      "nums": "99"
    },
    {
      "mystr": "another -2 example 99",
      "nums": "2"
    },
    {
      "mystr": "another-8766 example 99",
      "nums": "8766"
    },
    {
      "mystr": "http://somedomain.com/2015/12/this-is-a-post-with-id-in-url-99999",
      "nums": "2015"
    },
    {
      "mystr": "http://somedomain.com/2015/12/this-is-a-post-with-id-in-url-99999/gallery/001",
      "nums": "2015"
    },
    {
      "mystr": "http://somedomain.com/2015/12/this-is-a-post-with-id-in-url-99999/print-preview",
      "nums": "2015"
    }
  ]
  </code></pre>