Getting all the occurrences of a substring within a string

Question

How can I get all the occurrences of a substring within a string in PostgreSQL?

I have this string for an ID:

BS Score xxxxxxx075SCxxxBS Score xxxxxxx062SCxxxBS Score xxxxxxx115SCxxx

And I would like to get the numbers in the string for the ID, so the result can look like this:

Answer 1

You can use regexp_matches:

select id intl, regexp_replace(v[1], '^0+', '') values from tbl 
cross join regexp_matches(id, '(\d+)', 'g') v

See fiddle.