Skip to Content
0
May 09, 2018 at 04:57 PM

String Without Separators to Decimal Conversion

66 Views Last edit May 09, 2018 at 05:01 PM 2 rev

This seems like a really simple thing to fix but for the life of me I can't find the solution. I'm processing a file from a merchant that has some dollar amounts in it, which look like this:

1234500000
1230000000
1234567800

I have the output column set to decimal(10,2), and a conversion function in the mapping to_decimal(substr(Record,51,10),'.',',',2) but the output is always two decimal places off. I tried changing the substr length to 8, and the last two digits are replaced by zeroes.

Surely someone has come across this issue in the past. It seems really straightforward but I'm not getting the functions to work properly--even with variable precision it shouldn't be this difficult.