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
May 10, 2022 at 3:54 pm
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.
May 10, 2022 at 4:49 pm
As Phil noted, input types matter
DECLARE @i INT = 100
, @j NUMERIC(7, 4)
SELECT @j = @i
SELECT @i, @j
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
May 11, 2022 at 5:00 am
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
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!
May 11, 2022 at 3:23 pm
Thank You Phil - Sorry for the late reply - Input Data Type is varchar and the max precision is 4.
May 11, 2022 at 3:35 pm
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.
May 11, 2022 at 3:54 pm
Thank you Scott for you solution !!
May 11, 2022 at 4:17 pm
;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