Right justify numeric columns

• Gerbera

  SSCommitted

  Points: 1715

  More actions

  April 16, 2012 at 7:40 am

  #143299

  i need to right justify numeric columns before writing it to the flat file.

  Can anybody help?

  thanks

• Michael Valentine Jones

  SSC Guru

  Points: 64818

  More actions

  April 16, 2012 at 8:13 am

  #1474050

  Here is how to do it in your T-SQL query.

  select

  a.MyNum,

  ZeroFilled =

  case when a.MyNum < 0 then '-' else ' ' end+

  right('00000000'+convert(varchar(11),abs(a.MyNum)),11),

  SpaceFilled =

  right(' '+convert(varchar(12),a.MyNum),12)

  from

  ( -- Test Data

  select MyNum = convert(numeric(10,2),123.01)union all

  select MyNum = convert(numeric(10,2),-87654321.00)union all

  select MyNum = convert(numeric(10,2),123000.01)union all

  select MyNum = convert(numeric(10,2),-7655.01)union all

  select MyNum = convert(numeric(10,2),0.01)union all

  select MyNum = convert(numeric(10,2),-0.01)union all

  select MyNum = convert(numeric(10,2),4507234.01)

  ) a

  Results:

  MyNum ZeroFilled SpaceFilled

  ------------ ------------ ------------

  123.01 00000123.01 123.01

  -87654321.00 -87654321.00 -87654321.00

  123000.01 00123000.01 123000.01

  -7655.01 -00007655.01 -7655.01

  .01 00000000.01 0.01

  -.01 -00000000.01 -0.01

  4507234.01 04507234.01 4507234.01

  (7 row(s) affected)