Post reply

Padding zeros after decimal

  • January 29, 2014 at 4:26 pm

    #285061

    i have a table like

    [A]-------------

    [0]-------------[0.012345]

    [1]-------------[0.002345]

    [0.2145]------[0.1457]

    I need both columns A and B to be padded with zeros at the end upto 9th place after decimal so the output should look like:

    [A]------------------------

    [0.000000000]-----------[0.012345000]

    [1.000000000]-----------[0.002345670]

    [0.214500000]-----------[0.145700000]

    NOte: total number of digits = 10 not including '.'decimal

    i tried this but just getting 11 zeros.. it work when i want zeros in the left but not on the right

    select right(REPLICATE('0', 11) + CAST(

    AS VARCHAR(11)),11)

    from #table

  • January 29, 2014 at 4:31 pm

    #1684504

    Could you post DDL for the columns involved?

    EDIT: I'm asking because data types might perform different one from another and it would end in a wrong solution.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • January 29, 2014 at 4:58 pm

    #1684505

    Hey... can anyone tell me the best way to migrate Reporting Services 2008 (not 2008 R2) to 2012. The Microsoft migration tool does not work... Just wondering what the best way might be...

  • January 29, 2014 at 5:21 pm

    #1684506

    sqlsean (1/29/2014)

    i have a table like

    [A]-------------

    [0]-------------[0.012345]

    [1]-------------[0.002345]

    [0.2145]------[0.1457]

    I need both columns A and B to be padded with zeros at the end upto 9th place after decimal so the output should look like:

    [A]------------------------

    [0.000000000]-----------[0.012345000]

    [1.000000000]-----------[0.002345670]

    [0.214500000]-----------[0.145700000]

    NOte: total number of digits = 10 not including '.'decimal

    i tried this but just getting 11 zeros.. it work when i want zeros in the left but not on the right

    select right(REPLICATE('0', 11) + CAST(

    AS VARCHAR(11)),11)

    from #table

    Just convert it to DECIMAL(19,9)

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • January 29, 2014 at 5:22 pm

    #1684507

    james.grondalski (1/29/2014)

    Hey... can anyone tell me the best way to migrate Reporting Services 2008 (not 2008 R2) to 2012. The Microsoft migration tool does not work... Just wondering what the best way might be...

    I'd recommend starting your own post with the appropriate title on it to catch the attention of folks that might know something about this.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 5 posts - 1 through 4 (of 4 total)

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