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
One way would be to
REVERSE the decimal and use
SELECT POWER(10, PATINDEX('%.%', REVERSE(val)) - PATINDEX('%[^0]%', REVERSE(val))) from (values(0.604000), (0.600000), (0.000000), (4.000000) )v(val)
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).