How to replace a string in Transform stage?

[vsanghvi]

I am reading a data from DB2 and need to put into Oracle. My design is DB2 ->Transform Stage -> Oracle. Once i read the data from DB2 I need to remove certain words from the row. For e.g. Let's say I have following rows.

1. XYZ Fund.
2. XYZ Variable Life Insurance Fund.
3. XYZ Variable Income fund.

I need to replace 'XYZ Variable Life Insurance' with 'VLI' and I need to replace 'XYZ Variable Income' with 'VI' and if it is only XYZ in the name then just remove it. So the result would like this.

1. Fund.
2. VLI Fund
3. VI Fund.

Now i can do this creating routine and I have done that successfully. My question is, is this possible in Transform stage itself ? Is there any other way to accomplish this without writing routine.

Thanks

[kcbland]

None more simple and elegant than the way you've done it. THe alternative is to paste your function logic into the derivation of the column, which would probably not fit well given the CASE and/or LOOP statement/ structure you've probably used in the function.

[ray.wurlod]

Welcome aboard! :D

I agree with Ken. Chances are that your routine code could be streamlined, for example with judicious use of the Ereplace() function.

You can post your code here if you'd like us to look at it, and it does not violate any non-disclosure agreement you may have in place. If you do, selected the entire body of code, then click on the Code button above.

[vsanghvi]

Thanks kcbland and Ray. I thought that the routine is the only way but then i thought may be there is a way and I don't know. So i asked here and you guys confirmed that that is the best way. I will go ahead with routines only. Again thanks for your help.