Get the number of digits after the decimal point of a float (with or without decimal part)

Question

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

Possible duplicate of [How do I count decimal places in SQL?](http://stackoverflow.com/questions/14715141/how-do-i-count-decimal-places-in-sql) — artm, May 04 '16 at 10:15
select len(substring('123.789456',charindex('.','123.789456')+1,len('123.789456'))) — KumarHarsh, May 04 '16 at 10:17
@artm, Data type if float . declare @v float set @v=0.0012 select SQL_VARIANT_PROPERTY(@v, 'Scale') as Scale , result is = 0 ?? — Abdul Rasheed, May 04 '16 at 10:18
@KumarHarsh : It will return 3 for the non-decimal value `select len(substring('123',charindex('.','123')+1,len('123')))` — Arulkumar, May 04 '16 at 10:19
@artm, can u plz just share the query for that fit in above list(float datatype) ? that wld be very helpful. — Abdul Rasheed, May 04 '16 at 10:20
@jarlh, You are correct, but i got a request for it from another team, i don't know what is the use of this Query for them . I am looking for a relatively simple query for this. — Abdul Rasheed, May 04 '16 at 10:57
Is anything i missed here, why this question have 2 down-votes. If you comment here please , then i can improve as much i can possible, and also that can be helpful for my future posts. (And this is not a duplicate, i didn't find any of the solution for this from the above mentioned posts by @artm). — Abdul Rasheed, May 04 '16 at 11:37
I found some simple script (relatively to me) to handle this, answer below http://stackoverflow.com/questions/37024739/get-the-number-of-digits-after-the-decimal-point-of-a-float-with-or-without-dec/37027599#37027599 — Abdul Rasheed, May 05 '16 at 08:50

Stanislovas Kalašnikovas · Answer 1 · 2016-05-04T10:33:48.840

3

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

edited May 04 '16 at 10:33

answered May 04 '16 at 10:20

Stanislovas Kalašnikovas

9,203
4
32
59

1

It will fail if the float doesn't have any decimal. `DECLARE @Float FLOAT = 123; SELECT LEN(CONVERT(INT,CONVERT(FLOAT,REVERSE(CONVERT(VARCHAR(50), @float, 128))))) as Result;` – Arulkumar May 04 '16 at 10:23
@Arulkumar thank you for your notice, fixed It, updated answer. – Stanislovas Kalašnikovas May 04 '16 at 10:26
sure, so far this is good for me(but am waiting if there is any simple query without this much conversions and cast - may be :) ) – Abdul Rasheed May 04 '16 at 10:49
1

For a general solution try ABS() for the Amount, because it fails on negative values :) – AjV Jsy Feb 04 '19 at 17:45

score 2 · Accepted Answer · answered May 04 '16 at 12:18

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

KumarHarsh · Answer 3 · 2016-05-05T05:08:12.583

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

score 0 · Answer 4 · edited May 04 '16 at 10:40

0

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]

edited May 04 '16 at 10:40

Arulkumar

12,966
14
47
68

answered May 04 '16 at 10:34

Pankaj Kumar

57
4

score 0 · Answer 5 · answered May 04 '16 at 12:28

0

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

answered May 04 '16 at 12:28

gofr1

15,741
11
42
52

Thanks for your replay, good one, but mine one is relatively simple i think (so far), and then @Stanislovas Kalašnikovas answer. – Abdul Rasheed May 04 '16 at 12:34
1

My pleasure! I've seen your reply after posting my answer, it looks much more simple indeed. – gofr1 May 04 '16 at 12:40

score 0 · Answer 6 · answered Oct 08 '18 at 14:24

0

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

answered Oct 08 '18 at 14:24

Hassan Voyeau

3,383
4
22
24

please check the result of '1234.1', is the above function return correct result? – Abdul Rasheed Oct 09 '18 at 04:09
As I said. I was forced to put my answer here. The answer was intended for the other question which was marked as duplicate. Simply adjust this answer to suit your needs. – Hassan Voyeau Oct 09 '18 at 04:25

Get the number of digits after the decimal point of a float (with or without decimal part)

6 Answers6

Linked

Related