How know how much digits has a decimal number inside a value

August 24, 2023

I’m new about sql and trying to know how much decimal number I have inside a number.
So for example if I have 0.64 it will be 2 decimal, if I have 0.604 it will be 3 decimal.
But if I search for first zero it will find 0 between 6 and 4. I tried using RIGHT to search from right zero but didn’t work.

Why that? Because I need to know how much 0 I need to multiply to my decimal to have an integer but at same time I need to know this number of 0 so I put inside another field.

So for example, if I have 0.64 I will multiply to 100 so I will have 64 in a filed and 100 in another.
If I have 0.604 I will multiply to 1000 so I will have 604 in a field and 1000 in another.

I’m doing a migration from an old ERP to SAP.

Instruction that I’m trying

declare @val decimal(10,6),
        @var varchar(50),
        @point int,
        @zero int,
        @calc int,
        @UMREZ int,
        @UMREN int

set @val = 0.604 
set @var = trim(str(@val, 10, 10))
set @point = cast(CHARINDEX('.', @var) as int)
set @zero = cast(right(CHARINDEX('0', @var, @point), 8) as int)
set @calc = @zero - @point - 1

set @umrez = case when @calc = 0 then 1 
                  when @calc = 1 then 10 
                  when @calc = 2 then 100
                  when @calc = 3 then 1000
                  when @calc = 4 then 10000
                  when @calc = 5 then 100000
                  when @calc = 6 then 1000000
         end
set @UMREN = @val * @UMREZ

select @var, @point, @zero, @calc, @UMREZ, @UMREN

>Solution :

One way would be to REVERSE the decimal and use patindex

SELECT POWER(10, PATINDEX('%.%', REVERSE(val)) - PATINDEX('%[^0]%', REVERSE(val)))
from 
(values(0.604000),
       (0.600000),
       (0.000000),
       (4.000000)
)v(val)

The PATINDEX('%.%', REVERSE(val)) can just be replaced with 7 if you are working with decimal(x, 6) and don’t have to worry about it changing in the future (e.g. as this a one off task).

Fiddle