Help with substring

• tschuler-738392

  Ten Centuries

  Points: 1172

  More actions

  February 24, 2012 at 11:45 am

  #265224

  I have a table that has a column named code. In this table i have data which is all in 5.4 format. Example is 11111.1111. I need to add zeros to the front of each number like this: 011111.01111

  I am using the following but only get a 0 added to the first part.

  select code, ( '0' + (left(code,5) + substring(code,6,6) ))

  from accountmain

  I am not sure how to complete this so that a 0 gets added after the .

  Any help is appreciated.

• calvo

  SSChampion

  Points: 12930

  More actions

  February 24, 2012 at 11:57 am

  #1451539

  depending on your business rules, it could be as simple as a concatenation and replace function.

  select '0'+REPLACE(code,'.','.0') from

  This concatenates a '0' on the front and replaces every '.' with '.0' which i think gets you what you want.

  If you have more complicated requirements, I bet a nifty splitter and tally table would do wonders!

  ______________________________________________________________________________________________
  Forum posting etiquette.[/url] Get your answers faster.

• tschuler-738392

  Ten Centuries

  Points: 1172

  More actions

  February 24, 2012 at 12:21 pm

  #1451552

  Thank you very much. Almost too easy, thanks for a second set of eyes on this.