Hello,
I have to substring following string, its one column, into three columns to pass arguments in an api to get invoice number.
| COMPANY=01^LUMP_SUM_TRANS_ID=2^MIXED_PAYMENT_ID=4637^MIXED_PAYMENT_LEDGER_TRANS_ID=25^ |
| COMPANY=01^LUMP_SUM_TRANS_ID=20^MIXED_PAYMENT_ID=4637^MIXED_PAYMENT_LEDGER_TRANS_ID=1^ |
Desired result
| company | LUMP_SUM_TRANS_ID | MIXED_PAYMENT_ID | MIXED_PAYMENT_LEDGER_TRANS_ID |
| 01 | 2 | 4637 | 25 |
| 01 | 20 | 4637 | 1 |
Now you see lump_sum_trans_id has can have 1 or 2 digits and same with Mixed_payment_ledger_trans_id can be 1 or 2 characters.
Company and mixed_payment_id is mostly same number of characters.
Previously I used decoded with substring on the assumption that all columns are using same characters….. but now situation is changed.
I understand regexp_substr or instr can be helpful but I am lost in it. some expert help will save my day :)
Thanks
