Environment:
Oracle 12c EE on Exadata Linux (12.1.0.2)
I have a table with a column that has sets or ranges of numbers like this:
4848444 R 8151-8151 8153-8153 0070-0073 8154-8155 0080-0084
4848445 R 8151-8151 8153-8153 0070-0073 8154-8155 0080-0084
4848446 R 8151-8151 8153-8153 0070-0073 8154-8155 0080-0084
4848463 R 8051-8051 0309-0309 8100-8108 8130-8139
4848464 R 8051-8051 0309-0309 8100-8108 8130-8139
4848469 R 0RG10J0-0RG137J 0RG13J0-0RG207J 0RG20J0-0RG237J 0RG23J0-0RG247J 0RG24J0-0RG407J 0RG40J0-0RG437J 0RG43J0-0RG447J 0RG44J0-0RG44ZJ 0RG60J0-0RG637J
4848470 R 0RG10J0-0RG137J 0RG13J0-0RG207J 0RG20J0-0RG237J 0RG23J0-0RG247J 0RG24J0-0RG407J 0RG40J0-0RG437J 0RG43J0-0RG447J 0RG44J0-0RG44ZJ 0RG60J0-0RG637J
4848471 R 0RG63J0-0RG647J 0RG64J0-0RG707J 0RG70J0-0RG737J 0RG73J0-0RG747J 0RG74J0-0RG807J 0RG80J0-0RG837J 0RG83J0-0RG847J 0RG84J0-0RG84ZJ 0RGA070-0RGA071
I would like to pivot this data and separate out the first number (min) from the second number (max) in each set of ranges.
I gotta feel this is a great place for REGEXP_SUBSTR. I am NOT a REGEXP expert and have been reading and experimenting with different formulas but so far with no luck.
I would like to end up with a table with columns: row_key (4848444), min_value and max_value.
I will insert a sequence number as a PK for each row.
The resulting table would look something like:
KEY SEQ MIN MAX
------------ ------- ------------- -------------
4848444 1 8151 8151
4848444 2 8153 8153
4848444 3 0070 0073
4848444 4 8154 8155
4848444 5 0080 0084
.
4848463 10 8051 8051
4848463 11 0309 0309
4848463 12 8100 8108
4848463 13 8130 8139
.
4848469 20 0RG10J0 0RG137J
4848469 21 0RG13J0 0RG207J
4848469 22 0RG20J0 0RG237J
4848469 23 0RG23J0 0RG247J
4848469 24 0RG24J0 0RG407J
4848469 25 0RG40J0 0RG437J
.
.
.
There is a different number of sets of ranges in each row.
Any help is greatly appreciated!!
-gary