Skip to Content

String Without Separators to Decimal Conversion

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.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    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:

    substr(Record,51,10)/100
    
    Add comment
    10|10000 characters needed characters exceeded