Converting data- to -data (eg.: 1000.50- to -1000.50) in Each Informatica Job Run

Hi all,
I'm having data in a source file (txt file) in the format XXX.YYY- (Ex.: 1000.50-).
Now i need to load this to target Oracle table in the Format -XXX.YYY(EX.: -1000.50) .
1000.504- to -1000.504
I need to do this generically, since the data may differ either in 3 digits or 4 digits or 5 digits and the decimal also may differ.

An option could be that you define or cast the data into decimal,
informatica will complete the with 0 at the end, an in another field you
could ask if is <>0 then subtring (field, 1,2)||','
||subrstring(field,3,5) and multiply by -1 .

Hope it works!

2012/7/2 alg888 email@removed

> **
> Question from alg888 on Jul 2 at 9:37 AM Hi all,
> I'm having data in a source file (txt file) in the format XXX.YYY- (Ex.:
> 1000.50-).
> Now i need to load this to target Oracle table in the Format -XXX.YYY(EX.:
> -1000.50) .
> 1000.504- to -1000.504
> I need to do this generically, since the data may differ either in 3
> digits or 4 digits or 5 digits and the decimal also may differ.
>
> Kindly advice on this. Thanks in advance..
>
> *Reply to this email* to post your response.

I tried to do this with the reg_replace function which should handle Perl Compatible Reqular Expressions (PCRE:s) but I didn't get it to work. For example:

reg_replace(
'11.23-',
'([\d.]+)-',
'-\1'
)

should convert 11.23- to -11.23 but it returns -\1.

I tested the patterns with The Regex Coach and they work there. The Informatica documentation doesn't give any examples of how to capture groups let alone use named groups that are also part of PCRE. Does anyone now if these are implemented in Informatica (we are running 9.1.0)?

Copyright 1998-2015 Ziff Davis, LLC (Toolbox.com). All rights reserved. All product names are trademarks of their respective companies. Toolbox.com is not
affiliated with or endorsed by any company listed at this site.