spliting the string in pl/sql

Question

Hi I want to divide below string in 3 parts. I am using INSTR and SUBSTR. is there any better way to do it?

DEUT TRA USD RM TRAD UK, GB02DEUT40508124381319 (USD)

output i want

account_name=DEUT TRA USD RM TRAD UK
account_number=GB02DEUT40508124381319
account_currency=USD

I am using below code for the same.

 FCBD(i)='DEUT TRA USD RM TRAD UK, GB02DEUT40508124381319 (USD)',

account_name := substr(FCBD(i),1,INSTR(FCBD(i),',',1,1)-1);
dbms_output.put_line('Account Name:'||account_name);

account_number := substr(FCBD(i),INSTR(FCBD(i),',',1,1)+2,INSTR(FCBD(i),' ',INSTR(FCBD(i),',',1,1)+2,1)-INSTR(FCBD(i),',',1,1)-2);
dbms_output.put_line('Account Number:'||account_number);

account_currency := substr(FCBD(i),INSTR(FCBD(i),'(',1,1)+1,INSTR(FCBD(i),')',1,1) - INSTR(FCBD(i),'(',1,1)-1);

Is there any better way to do it?

Answer 1

I would recommend regexp_substr():

select trim(regexp_substr(fcbd, '^[^,]+')) as account_name,
       replace(regexp_substr(fcbd, ', [^ ]+'), ', ', '') as account_number,
       replace(regexp_substr(fcbd, '[^)]{3}\)$'), ')', '') as account_currency
from t;

Here is a db<>fiddle.