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

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