Bigquery - Regex To Match Number Of 8 Digits After A Known String

I need to extract 8 digits after a known string: | MyString | Extract: | | ---------------------------- | -------- | | mypasswordis 12345678 | 12345678

Solution 1:

You need a capturing group here to extract a part of a pattern, see the REGEXP_EXTRACT docs you linked to:

If the regular expression contains a capturing group, the function returns the substring that is matched by that capturing group. If the expression does not contain a capturing group, the function returns the entire matching substring.

Also, the .* pattern is too costly, you only need to match whitespace between the word and the digits.

Use

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

Or just

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

See the re2 regex online test.

Solution 2:

Try to not use regexp as much as you can, its quite slow. Try substring and instr as example:

SELECT SUBSTR(MyString, INSTR(MyString,'mypasswordis') + LENGTH('mypasswordis')+1)

otherwise Wiktor Stribiżew have probably right answer.