declare @var varchar(100),@pre int
select @var = '0.0123456789012345',@pre = 2
declare @sql nvarchar(100)
select @sql = 'select CONVERT(decimal(18,'+convert(varchar,@pre)+'),'''+@var+''')'
exec (@sql)
declare @var varchar(100),@pre int
select @var = '0.0123456789012345',@pre = 2
declare @sql nvarchar(100)
select @sql = 'select CONVERT(decimal(18,'+convert(varchar,@pre)+'),'''+@var+''')'
exec (@sql)
alter function dbo.fix(@num float, @digits int) returns varchar(1000) as
begin
declare @x varchar(1000)
declare @res varchar(1000)
set @x = cast(isnull(cast(@num as decimal(28,14)),0) as varchar(1000))
if @x like '.%' set @x = '0' + @x
set @res = ''
while @digits > 0
begin
if @x like '.[0-9]%' set @res = @res + left(@x,2)
if @x like '[0-9]%' set @res = @res + left(@x,1)
if @x = '' and @res like '%.%' set @res = @res + '0'
if @x = '' and @res not like '%.%' set @res = @res + '.0'
set @digits = @digits -1
if @x <> ''
begin
if @x like '.%' set @x = substring(@x, 2, 1000)
set @x = substring(@x, 2, 1000)
end
end
while @x like '[0-9]%.%' or ( @x <> '' and @res not like '%.%')
begin
set @res = @res + '0'
set @x = substring(@x, 2, 1000)
end
return (@res)
end
declare @dig int
select @dig = 2
create table #table(Num float)
insert #table values (1.234),(0.0100),(0.005),(0.94)
select *,ROUND(Num,@dig) RoundedValue
from #table
drop table #table
/*
Num RoundedValue
1.234 1.23
0.01 0.01
0.005 0.01
0.94 0.94
*/
CREATE FUNCTION dbo.fn_Fix
(@num FLOAT)
RETURNS VARCHAR(100)
AS
BEGIN
DECLARE @rn INT,
@str VARCHAR(100)
SELECT @rn = 0;
;WITH cte
AS (SELECT string,
SUBSTRING(string,n + 1,1) string_val,
n,
ROW_NUMBER()
OVER(ORDER BY n DESC) rn
FROM (SELECT SUBSTRING(CONVERT(VARCHAR(100),@num),3,LEN(CONVERT(VARCHAR(100),@num))) string) t1
CROSS JOIN (SELECT NUMBER
FROM MASTER..spt_values
WHERE TYPE = 'P') AS Numbers(n)
WHERE n < LEN(string))
SELECT @str = '0.' + c1.string + CASE
WHEN c2.string_val = 0 THEN '0'
ELSE ''
END
FROM cte c1
LEFT JOIN cte c2
ON c1.rn + 1 = c2.rn
WHERE c1.rn = 1
RETURN @str
END
And use this function in the code.declare @dig int
select @dig = 2
create table #table(Num float)
insert #table values (0.234),(0.0100),(0.005),(0.94)
select *,dbo.fn_fix(Num) RoundedValue
from #table
drop table #table
/*
Num RoundedValue
0.234 0.234
0.01 0.010
0.005 0.0050
0.94 0.94
*/
Limitation: This function returns 2 sig figs only.
alter function dbo.fix(@num float, @digits int) returns varchar(1000) as
begin
declare @x varchar(1000)
declare @res varchar(1000)
set @x = cast(isnull(cast(@num as decimal(28,14)),0) as varchar(1000))
if @x like '.%' set @x = '0' + @x
set @res = ''
while @digits > 0
begin
if @x like '.[0-9]%' set @res = @res + left(@x,2)
if @x like '[0-9]%' set @res = @res + left(@x,1)
if @x = '' and @res like '%.%' set @res = @res + '0'
if @x = '' and @res not like '%.%' set @res = @res + '.0'
set @digits = @digits - case when replace(replace(@res, '0', ''), '.', '') <> '' then 1 else 0 end
if @x <> ''
begin
if @x like '.%' set @x = substring(@x, 2, 1000)
set @x = substring(@x, 2, 1000)
end
end
while @x like '[0-9]%.%' or ( @x <> '' and @res not like '%.%')
begin
set @res = @res + '0'
set @x = substring(@x, 2, 1000)
end
return (@res)
end
alter function dbo.fix(@num float, @digits int) returns varchar(1000) as
begin
declare @x varchar(1000)
declare @res varchar(1000)
set @x = cast(isnull(cast(@num as decimal(28,14)),0) as varchar(1000))
if @x like '.%' set @x = '0' + @x
set @res = ''
while @digits > 0
begin
if @x like '.[0-9]%' set @res = @res + left(@x,2)
if @x like '[0-9]%' set @res = @res + left(@x,1)
if @x = '' and @res like '%.%' set @res = @res + '0'
if @x = '' and @res not like '%.%' set @res = @res + '.0'
set @digits = @digits - case when replace(replace(@res, '0', ''), '.', '') <> '' then 1 else 0 end
if @x <> ''
begin
if @x like '.%' set @x = substring(@x, 2, 1000)
set @x = substring(@x, 2, 1000)
end
end
while @x like '[0-9]%.%' or ( @x <> '' and @x not like '.%' and @res not like '%.%')
begin
set @res = @res + '0'
set @x = substring(@x, 2, 1000)
end
return (@res)
end
If you are experiencing a similar issue, please ask a related question
Join the community of 500,000 technology professionals and ask your questions.
Connect with top rated Experts
30 Experts available now in Live!