Get the number of digits after the decimal point o

问题:

I have following list of Amount (float) in my table.

Amount
123
123.1
123.0123
123.789456

How can i get the number of digits after the decimal point.

Duplicate ?: I have checked already existing posts, but there is no correct way to handle the float numbers with or without decimal part.

Result

Amount      Result
123         0
123.1       1
123.0123    4
123.789456  6

EDIT : After spending some valuable time, i have found some relatively simple script to handle this. My answer is below

回答1:

You can do It in following:

QUERY

SELECT Amount, 
       CASE WHEN FLOOR(Amount) <> CEILING(Amount) THEN LEN(CONVERT(INT,CONVERT(FLOAT,REVERSE(CONVERT(VARCHAR(50), Amount, 128))))) ELSE 0 END AS Result
FROM YourTable

OUPUT

Amount      Result
123         0
123,1       1
123,0123    4
123,789456  6

回答2:

I found some simple script (relatively to me) to handle this.

ISNULL(NULLIF(CHARINDEX('.',REVERSE(CONVERT(VARCHAR(50), Amount, 128))),0) - 1,0)

Here the ISNULL(NULLIF is only to handle the float without decimal part. If there is no values without decimal part, then it is very simple

CHARINDEX('.',REVERSE(CONVERT(VARCHAR(50), Amount, 128))) -1 

Hope this will be helpful to you. Full script below

declare @YourTable table (Amount float)
insert into @YourTable
values(123),(123.1),(123.0123),(123.789456)

SELECT  ISNULL(NULLIF(CHARINDEX('.',REVERSE(CONVERT(VARCHAR(50), Amount, 128))),0) - 1,0)
FROM    @YourTable

SELECT  CHARINDEX('.',REVERSE(CONVERT(VARCHAR(50), Amount, 128))) -1 
FROM    @YourTable

回答3:

Abdul Thanks for pointing my mistake. I was not aware of peculiar behaviour of float.

declare @t table(Amount float)
insert into @t values (123),(123.1),(123.0123),(123.789456)

Here see this line will do the trick,

select CONVERT (VARCHAR(50), amount,128) from @t

Complete script,

select col ,case when len(col)>0 then len(col) else 0 end newcol from
(SELECT substring(REVERSE(CONVERT (VARCHAR(50), amount,128)),0,
charindex('.',REVERSE(CONVERT (VARCHAR(50), amount,128)))) col from @t)t4

回答4:

This code will definitely help you.

SELECT Amount,
       LEN(SUBSTRING(CAST(Amount as VARCHAR),CHARINDEX('.',CAST(Amount as VARCHAR))+1,LEN(Amount)))  Result
FROM [Your Table Name]

回答5:

And one more way:

SELECT  Amount,
        CASE WHEN deci = 0 THEN 0 ELSE LEN(deci) END AS Result
FROM (
    SELECT  Amount,
            TRY_CAST(REVERSE(REPLACE(Amount - TRY_CAST(Amount as int),'0.','')) as int) as deci
    FROM (VALUES
    (123),
    (123.1),
    (123.0123),
    (123.789456)
    ) as t (Amount)
) as t

Output:

Amount      Result
123.000000  0
123.100000  1
123.012300  4
123.789456  6

回答6:

Since this was said to be a duplicate of this question - SQL Server how to get money type's decimal digits? - I have no choice to put my answer here.

/* Assumes money data type is passed in and therefore caters for overflow */
CREATE FUNCTION dbo.CountDecimalPlaces (@value decimal(30,4))  
RETURNS int
AS  
BEGIN 
    DECLARE @part decimal(30,4);
    SET @part = ABS(@value - CAST(@value as bigint));
    RETURN LEN(FORMAT(@part, '0.00##'))-2
END