• Hello,

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".

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)