Post reply

Fixed width problem

  • September 9, 2004 at 8:26 am

    #66203

    If have a dts package that creates a fixed length file from an sql server table. I need one of the fields to be aligned to the right within the text file. All sql server fields are of type char.

    111 2222   333 

    1    2222   333

    111 2        333

    this is what is needed in the file, the middle column aligned to the right:

    111    2222333 

    1       2222333

    111         2333

    Is this possible?

    Thanks for any help

     

  • September 10, 2004 at 7:02 am

    #522529

    UPDATE TABLE1 SET Field1 = REPLLICATE('', 7-LEN(LTRIM(RTRIM(Field1)))) + LTRIM(RTRIM(Field1))

  • September 10, 2004 at 7:06 am

    #522531

    Wouldn't let me edit:

     

    UPDATE TABLE1 SET Field1 = REPLLICATE(' ', 7-LEN(LTRIM(RTRIM(Field1)))) + LTRIM(RTRIM(Field1))

  • September 10, 2004 at 9:23 am

    #522576

    You might want to put the reformatting in a view, a transformation in the DTS package, or a computed column in the table, rather than updating the table.  This way it applies to data added in future UPDATE/INSERT actions.

    I doubt there is a performance difference, but I prefer this formulation for readablility:

    RIGHT('       ' + RTRIM(Field1), 7)

     

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

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