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

Question

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)', ????)

What is your exact SQL database? – Tim Biegeleisen Sep 21 '22 at 23:42 — Tim Biegeleisen, Sep 21 '22 at 23:42

score 1 · Answer 1 · answered Sep 21 '22 at 23:31

1

Try this "(?<=serial().[0-9]+"

answered Sep 21 '22 at 23:31

jak dev

Thank you. I tried it and received this warning: "Invalid regular expression: '(?<=serial().[0-9]+', no argument for repetition operator: ?" – Drake Sep 21 '22 at 23:51
Weird I pasted it with this but it looks like it did not show up... You need to escape the ( with a backslash \ – jak dev Sep 22 '22 at 00:00

score 1 · Accepted Answer · answered Sep 21 '22 at 23:56

1

We can use REGEXP_SUBSTR with a capture group:

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

answered Sep 21 '22 at 23:56

Tim Biegeleisen

2 Answers2