Given a url like https://xyz.abc.yahoo.com/issues/80483987/tasks/1
How can write a SQL query to replace/extract urls like this with their integer ID that is 80483987
Using REGEXP_REPLACE(mystr, r'[^d]+', ' ') gives me 80483987 1
The 1 at the end being a problem.
Advertisement
Answer
You may simply REGEXP_EXTRACT the numbers after /issues/ substring:
REGEXP_EXTRACT(mystr, r'/issues/([0-9]+)')
See the regex demo.
The /issues/ will get matched and ([0-9]+) will capture 1 or more digits into capturing group #1 and that is the value returned by REGEXP_EXTRACT.