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?
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.