Hi,
Re: Oracle SQL
I have a description column in an Oracle EBS interface with data coming from an external system. As we see it contains a concatenation of fields/values from the external system.
Receipt Number
Document Number
Receipt Method Name
Receipt Date
Bank Name
Bank Account Name
I would like to separate this single column into multiple columns at the SQL level.
Note:
- The Receipt Number is in a different format on some lines e.g. WRE-2109-1, 2921-1, LBX-27732-1 etc... so the Document Number "header" next doesn't always start from a fixed X position within the string. The same note applies to the other "field headers" within the column.
- The Document Number "header" doesn't have any value now but it might be populated in the future.
This is what I would like the data to look like once extracted:
I am thinking that the regexp_substr might help but I haven't figured how to use it yet.
select 'Receipt Number - WRE-1654-1 Document Number - Receipt Method Name - US001 JPMC USD WIRE Receipt Date - 20-AUG-20 Bank Name - JPMORGAN CHASE BANK, N.A. Bank Account Name - CFSC-Chase NY-WIRE' as Receipt_Number
from dual;
Receipt_Number
----------------------
WRE-1654-1
select regexp_substr('Receipt Number - WRE-1654-1 Document Number - Receipt Method Name - US001 JPMC USD WIRE Receipt Date - 20-AUG-20 Bank Name - JPMORGAN CHASE BANK, N.A. Bank Account Name - CFSC-Chase NY-WIRE') as Receipt_Method_Name
from dual;
Receipt_Method_Name
--------------------------------
US001 JPMC USD WIRE
I've attached the screenshots in case they are hard to read.
Can you help me?
Many thanks
Dave