Jun 27, 2006

Function to get the fraction given a decimal value

This User-defined function, written in SQL Server 2000, which uses Euclid’s algorithm to convert the decimal to a fraction. The function has an accuracy of 0.0001 i.e. the input decimal value is rounded off beyond this when it’s converted to a fraction. If you need a better accuracy change the function accordingly.

The function returns the fraction as a string (of course).
Here is the function definition:
create function get_fraction(@input decimal(18,10))
returns varchar(20)
as
begin

declare @a bigint,@b bigint,@c bigint,@d bigint,@e bigint
select @b = 10000,
-- increase this number to increase the accuracy
@a = @INPUT*@b,
@e = @a,
@c = @b,
@d = @b
while (@c != 0)
begin
set @d = @c
set @c = @e%@d
set @e = @d
end
return cast(@a/abs(@d) as varchar) + '/' +cast(@b/abs(@d) as varchar)
end


Now lets test it

select dbo.get_fraction(10.234)

Result
5117/500

select dbo.get_fraction(0.0124)

Result
31/2500

1 comments:

Rich said...

If you need to have 6.75 come out like 6 3/4 use this function


declare @a bigint,@b bigint,@c bigint,@d bigint,@e bigint, @originalinput decimal( 18,10)

Select @originalinput = @input
Select @input = @input - Floor( @input )


select @b = 10000, -- increase this number to increase the accuracy
@a = @INPUT*@b,
@e = @a,
@c = @b,
@d = @b
while (@c != 0)
begin
set @d = @c
set @c = @e%@d
set @e = @d
end
return Cast ( Floor( @originalinput ) as varchar ) + Case when @a/@d > 0 then ' ' + cast(@a/abs(@d) as varchar) + '/' +cast(@b/abs(@d) as varchar) else '' end

Post a Comment