01 Feb 21 · npack · #bigquery ·   Bookmark   ×

Regexp extract to match number of 8 digits after a known string in BigQuery

I need to extract 8 digits after a known string:

| MyString                     | Extract: | 
| ---------------------------- | -------- | 
| mypasswordis 12345678        | 12345678 | 
| # mypasswordis 12345678      | 12345678 | 
| foobar mypasswordis 12345678 | 12345678 |

Solution:

Use

SELECT REGEXP_EXTRACT(MyString, r"mypasswordis\s*([0-9]{8})"))

Or just

SELECT REGEXP_EXTRACT(MyString, r"mypasswordis\s*([0-9]+)"))

npack

posted on 01 Feb 21

Enjoy great content like this and a lot more !

Signup for a free account to write a post / comment / upvote posts. Its simple and takes less than 5 seconds