informaticainformatica-powercenteriics

How to handle multiple spaces in Informatica?

I have a field in a csv file which has values like:

243 56,
1 673 22,
33 45 789 15

Now, I want to load this file using IICS to a Mysql table but remove all the spaces except the last one. The last space needs to be replaced by a dot(.). So the data would look like:

243.56,
1673.22,
3345789.15

Please ignore the commas at the end of each value. I added it for clarity.

Solution

  • You can achieve this in Informatica IICS using Expression transformation. Try using this logic:

    REPLACESTR(1, SUBSTR(your_field, 1, LENGTH(your_field) - INSTR(REVERSE(your_field), ' ')), ' ', '' ) || '.' || SUBSTR(your_field, LENGTH(your_field) - INSTR(REVERSE(your_field), ' ') + 2)