Oracle REGEXP_SUBSTR - Extract value between string based on column value, and symbol

Question

I have the following columns, I would like to search Column 1 with the value from Column 2, and extract the between Column2 || '/' and the Comma afterwards.

Column1	Column2
Test Pack: 040/1,050/1,060/2,070/1,080/1	040
Test Pack: 040/1,050/1,060/2,070/1,080/1	050
Test Pack: 040/1,050/1,060/2,070/1,080/1	060
Test Pack: 040/1,050/1,060/2,070/1,080/1	070
Test Pack: 040/1,050/1,060/2,070/1,080/1	080

Output

Column1	Column2	Output
Test Pack: 040/1,050/1,060/2,070/1,080/1	040	1
Test Pack: 040/1,050/1,060/2,070/1,080/1	050	1
Test Pack: 040/1,050/1,060/2,070/1,080/1	060	2
Test Pack: 040/1,050/1,060/2,070/1,080/1	070	1
Test Pack: 040/1,050/1,060/2,070/1,080/1	080	1

I have tried to use Regexp_substr but not sure how to use Column 2 in the Pattern parameter. I think I should be able to do this with a complicated series of Substr + Instr, but wondering if there is a better way to do this?

Answer 1

You can use expression regexp_substr(column1, column2||'/([^,]*)', 1, 1, null, 1).

Regex like 060/([^,]*) will match literal string 060/ and everything what follows it, until first comma (if appears). Part after / will be captured into group number 1.

By default regexp_substr extracts full match, but with provided parameters, it extracts only content captured into group with number 1 (specifically last parameter does this). Details on parameters of regexp_substr here.

Fiddle here.

Demo of regex here.