Remove character from dynamic string

Question

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 1

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