Fixed width problem

• Dannygr

  SSC Eights!

  Points: 984

  More actions

  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

   

• RiverGuy

  SSC Journeyman

  Points: 90

  More actions

  September 10, 2004 at 7:02 am

  #522529

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

• RiverGuy

  SSC Journeyman

  Points: 90

  More actions

  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))

• Scott Coleman

  One Orange Chip

  Points: 27452

  More actions

  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)