Adding leading zeroes after the decimal

Question

Post reply

Adding leading zeroes after the decimal

sabarishbabu

Old Hand

Points: 371
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 2 years, 6 months ago by sabarishbabu.

ScottPletcher

SSC Guru

Points: 101098

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

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: 246960 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.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Steve Jones - SSC Editor SSC Guru Points: 736212 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: 1004340 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: 371 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: 246960 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.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

sabarishbabu Old Hand Points: 371 More actions · Answer 6

sabarishbabu

Old Hand

Points: 371

May 11, 2022 at 3:54 pm

#4031210

Thank you Scott for you solution !!

kaj SSCommitted Points: 1605 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;