I have a script which processes the data below and INSERTs/UPDATEs the table (including the fact that the header columns are mapped to the appropriate table fields)
DATE_TIME,AMOUNT,ORDER_ID,CUSTOMER_ID,TRANSACTION_NUMBER
06/29/2022 11:13,153000,1110611609661950,Gower, David,75711-0_257
06/29/2022 07:12,153000,5110607610517360,Wordsworth, William,75708-0_257
The problem that I have is that the script is not able to read the last name and first name because of the comma.
Last name and first name in the data file are: Gower, David and Wordsworth, William respectively
The script thinks since there is a comma present it is belongs to another field (TRANSACTION_NUMBER in this case leading to a runtime error).
Business wants the comma between last name and first name to be replaced with a '/' i.e., Gower, David should become Gower/David.
I will be approaching it in the following way:
IF v_index = 'CUSTOMER_ID' THEN
TAKE THE LAST NAME PLUS THE FIRST NAME AND PUT A / BETWEEN THE 2;
END IF;
For example, the following code that I wrote does the trick. However how can I do the same when one has multiple lines of code (so that the script understands that when it is Gower, David it should be Gower/David) which is separated by so many commas.
SET SERVEROUTPUT ON
declare
a varchar2(32767):= 'Gower,David';
b varchar2(32767);
begin
b:=regexp_replace(a,'\,+','/');
dbms_output.put_line(b);
end;
Gower/David
PL/SQL procedure successfully completed.
My question is how can I do that when I have multiple lines of data i.e., which should be the starting point and which should be the ending point. Please let me know.