Adding leading zeroes after the decimal

  • Hello,

    I have the following requirement to add leading zeros after the decimal from numeric string, please advice the same.

    Input  ->  OutPut
    100 -> 100.0000
    1.1 -> 1.0001
    1.10 -> 1.0010
    1.100 -> 1.0100
    200.00 -> 200.0000

    • This topic was modified 1 month, 2 weeks ago by  sabarishbabu.
  • What is the datatype of the Input variable? If numeric, the values 1.1, 1.10 and 1.100 are indistinguishable.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • As Phil noted, input types matter

    DECLARE @i INT = 100
    , @j NUMERIC(7, 4)
    SELECT @j = @i
    SELECT @i, @j
  • In a less subtle manner than Phil already explained, I'll tell you that it's flat-out not possible to have those values in a single numeric column.  The ONLY way they could actually exist in a single column is if the column had a character-based datatype.  What is the exact datatype of that column?  We need to know exactly because CHAR() could present a different problem than VARHAR().

    --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)
    Intro to Tally Tables and Functions

  • If you don't need to worry about possible blank chars in the data, you can remove the trimming.  I did it just as a safeguard.

    ;WITH test_data AS (
    SELECT '100' AS Input UNION ALL
    SELECT '1.1' UNION ALL
    SELECT '1.10' UNION ALL
    SELECT '1.100' UNION ALL
    SELECT '200.00'
    )
    SELECT
    Input,
    LEFT(InputTrimmed, position_of_decimal - 1) + '.' +
    LEFT('0000', 4 - LEN(SUBSTRING(InputTrimmed, position_of_decimal + 1, 4))) + SUBSTRING(InputTrimmed, position_of_decimal + 1, 4)
    FROM test_data
    CROSS APPLY (
    SELECT LTRIM(RTRIM(Input)) AS InputTrimmed
    ) AS ca1
    CROSS APPLY (
    SELECT CHARINDEX('.', InputTrimmed + '.') AS position_of_decimal
    ) AS ca2

    SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

  • Thank You Phil - Sorry for the late reply - Input Data Type is varchar and the  max precision is 4.

  • sabarishbabu wrote:

    Thank You Phil - Sorry for the late reply - Input Data Type is varchar and the  max precision is 4.

    No worries. Scott's solution should work for you.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Thank you Scott for you solution !!

  • ;WITH test_data AS (
    SELECT '100' AS Input UNION ALL
    SELECT '1.1' UNION ALL
    SELECT '1.10' UNION ALL
    SELECT '1.100' UNION ALL
    SELECT '200.00'
    )

    select
    Input,
    concat(left(Input,isnull(nullif(charindex('.',Input,1),0),256)-1), '.',right(concat('0000',right(Input,len(Input)-nullif(charindex('.',Input,1),0))),4)) as "Output"
    from test_data;

Viewing 9 posts - 1 through 8 (of 8 total)

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