Post reply

Help with substring

  • 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.

  • 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.

  • February 24, 2012 at 12:21 pm

    #1451552

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

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

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