Skip to Content

String Without Separators to Decimal Conversion

May 09 at 04:57 PM


avatar image

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:


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.

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Best Answer
Andrew Harderson May 09 at 09:22 PM

Okay, the solution was crazy simple. I set the data type to decimal(10,2), removed the to_decimal function in the mapping, and added /100 after the substr function:

10 |10000 characters needed characters left characters exceeded