Adding leading zeroes after the decimal

Question

Post reply

Adding leading zeroes after the decimal

sabarishbabu

Old Hand

Points: 366
More actions
May 10, 2022 at 11:25 am
Go to Answer
#4029973
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 year, 11 months ago by sabarishbabu.

ScottPletcher

SSC Guru

Points: 100947

May 11, 2022 at 2:49 pm

Answer

#4031205

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) A socialist is someone who will give you the shirt off *someone else's* back.

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

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

Phil Parkin SSC Guru Points: 246695 More actions · Answer 1

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.

Steve Jones - SSC Editor SSC Guru Points: 734449 More actions · Answer 2

As Phil noted, input types matter

DECLARE @i INT = 100
, @j NUMERIC(7, 4)
SELECT @j = @i
SELECT @i, @j

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 3

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)

sabarishbabu Old Hand Points: 366 More actions · Answer 4

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

Phil Parkin SSC Guru Points: 246695 More actions · Answer 5

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.

sabarishbabu Old Hand Points: 366 More actions · Answer 6

sabarishbabu

Old Hand

Points: 366

May 11, 2022 at 3:54 pm

#4031210

Thank you Scott for you solution !!

kaj SSCommitted Points: 1580 More actions · Answer 7

;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;