Extracting integer ID from URL in SQL

Question

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.

What makes 80483987 any different than any other number(s) ? No use trying to help you if you can't answer the question that is the core. — user557597
– user557597, Commented Jun 20, 2019 at 23:41

Wiktor Stribiżew · Accepted Answer · 2019-06-20 23:20:34Z

1

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.

answered Jun 20, 2019 at 23:20

Wiktor Stribiżew

631k41 gold badges502 silver badges632 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

collapsar · Accepted Answer · 2019-06-20 23:17:47Z

1

Apply REGEXP_REPLACE twice:

REGEXP_REPLACE(REGEXP_REPLACE(mystr, r'^[^\d]+', ''), r'/.*$', '')

Explanation

The inner call removes all leading non-digits, the outer call removes the suffix after the id. ^ and $ are so-called anchors and do not represent a character but the abstract (0-length) notions of 'beginning / end of the test string'. This will work in all common regex flavors and engines.

The solution hinges on the numerical id constituting a location segment in the url.

Note that the approach is fragile: eg. it will fail for urls with a port number.

answered Jun 20, 2019 at 23:17

collapsar

17.4k5 gold badges40 silver badges66 bronze badges

Collectives™ on Stack Overflow

Extracting integer ID from URL in SQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related