How to use regexp_substr() to return the numbers after a specific word in a string

I have a table column full of strings like this:

‘top-level:volume(1):semifinished(21491628):serial(21441769)’.

I would like to return just the numbers after ‘serial’ (i.e. ‘21441769’) using regex_substr().

select ('top-level:volume(1):semifinished(21491628):serial(21441769)', ????)

>Solution :

We can use REGEXP_SUBSTR with a capture group:

SELECT col, REGEXP_SUBSTR(col, 'serial\\((\\d+)\\)', 1, 1, 'e', 1) AS serial
FROM yourTable;