Remove character from dynamic string

I have a file with some junk values and i need to get rid of them while loading that file into a table. Giving here some example. File is semicolon separated, and last column has those junk values.

2019-02-20;05377378;ABC+xY+++Rohit Anita Chicago
2019-02-20;05201343;ABC+xY++Gustav Russia
2019-02-20;07348738;ABC+xy+++Jain Ram Ambarnath

Now the last column I have to load without ABC+xY+++ value. but some row i have ABC+xY+++ and some ABC+xY++. any suggestion to get rid of this. which means 2 times or 3 times + is available

I am using informatica powercenter for loading this file. in expression i need to create some substr/instr function. which i can put test here in oracle sql as well to understand quickly if the value coming is properly.

and my expected output is

Any suggestion please.

Thanks, Bithun

Answer

I’m not positive I understand your question, but this would do what I think you’re asking, would work in SQL and in an Infa expression as well.

with myrecs as
(select '2019-02-20;870789789707;ABC+xY++Gustav Russia' as myfield from dual union 
all
 select '2019-02-20;870789789707;ABC+xY+++Carroll Iowa' as myfield from dual)

 select myfield,

    substr(myfield,1, instr(myfield,';',-1)) ---will select everything up to, and including the final semicolon
    ||--concatenate
    substr(myfield,instr(myfield,'+',-1)+1) as yourfield --will select everything after the final plus sign
 from myrecs;

OUTPUT:
myfield                                         yourfield
2019-02-20;870789789707;ABC+xY++Gustav Russia   2019-02-20;870789789707;Gustav Russia
2019-02-20;870789789707;ABC+xY+++Carroll Iowa   2019-02-20;870789789707;Carroll Iowa