Post reply

transformation of amounts coming from flat text file

  • July 6, 2004 at 12:36 pm

    #108544

    Hello all. I wonder if any one can help me with the following:

    I am receiving a flat text file from a bank with some information I need to recon with the check table we have to keep payments history (check number, date, and amount must be the same otherwise the check is send to another table for accounting research.)

    The problem I am having is with the amounts, let's say the text file shows:

    0000004200 and the next one is 0000133041. I have to make the conversion to amounts in the regular money format (let's say 5.48)

    42.00 and 1330.41

    I used str to get rid of the leading zeros (the amounts are showing now as: 4200 and 133041), but I can get the decimal point to be inserted in the amount. Any suggestions will be greatly appreciated.

    And, did I mention it is urgent! Sorry, I got this one today and it is the beginning of the month and the developers needs to get this done by "yesterday"

    Thank you for your help

  • July 6, 2004 at 1:12 pm

    #513515

    See if this will work for you...

    declare

     @amt varchar(10),

     @fmt varchar(10),

     @flt float (2)

    set @amt = '0000133041'

    select @fmt = reverse(substring(reverse(@amt),1,2) + '.' +  substring(reverse(@amt),3,7))

    select @fmt as 'varchar'

    select @flt = cast(@fmt as float)

    select @flt as 'float'

  • July 6, 2004 at 2:02 pm

    #513520

    Thanks for your input. I will try that now.

  • July 7, 2004 at 2:01 am

    #513579

    Hi,

    Instead of doing all of that character manipulation, how about just converting it to float and dividing  by 100?   Once you cast a value, it discards any invalid preceding numeric characters and only uses the first set of numbers it stumbles upon .   I'd have bet this code would run significantly faster than manipulating the string, although the reverse thingy was pretty sneaksy, but after testing with 10000 iterations, they both took the same amount of time.   Oh well, more than one way to skin stuff (for all the cat lovers).

     

    declare

     @amt varchar(10),

     @flt float (2)

    set @amt = '0000133041'

    set @flt = cast(@amt as float)

    select @flt / 100

  • July 7, 2004 at 7:09 am

    #513629

    You know, when I did that "sneaksy" reverse stuff, I was thinking, "there's got to be a better, more direct way to do this."  I was obviously in a character string manipulation mode, though!  Good job, bobsterboy!

    Steve

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply