>-- this work with 1 or more decimals >select CHARINDEX('.',REVERSE(NULLIF(CAST(Value % 1 as varchar(40)))))-1 >-- with 0 decimals, CASE WHEN Value % 1=0 THEN 0 ... >Something is wrong at the last step - can you clarify?
declare @t table (Value float null) insert into @t select -41139.99 union all select 10062.99 union all select 1600000 union all select 134979.87 union all select -154062.04 union all select -153832.17 union all select -1235677.922308 union all select null select T.value, F.*, F1.*, F2.* from @t T cross apply (select CAST(Value as decimal(38,11)) as nValue) F cross apply (select CAST(nValue % 1 as varchar(40)) as cValue) F1 cross apply (select CHARINDEX('.',REVERSE(cValue)) as DecimalPointPos) F2I think we need to search for 0, not for .