Remove first character and add trailing zero

• helal.mobasher 13209

  SSCommitted

  Points: 1526

  More actions

  October 17, 2012 at 5:26 pm

  #283164

  y data in column A look like:

  -0002

  00030

  -0004

  00050

  I need to remove the first character '-' when occurs and then add a trailing zero so result is:

  00020

  00030

  00040

  00050

  Thank You for your help

  HM

• Mark Eckeard

  Hall of Fame

  Points: 3462

  More actions

  October 17, 2012 at 5:53 pm

  #1550257

  Replace the field and table name with your own and this should get you close:

  select

  (case LEFT([Value], 1)

  when '-' then substring([Value], 2, LEN([Value])-1) + '0'

  else Value

  end) as ValueNew

  from dbo.[Values]

  order by Value

  Mark