Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.
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
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Open in new window