Convert decima to fraction

Question

I would like to convert my decimals into fractions or find the Lowest Common Factor for two intergers. Besides using a function is there any code that I can write.

Answer 1

I think the highest common factor is more making sense. To get that, you can have a loop to count from 1 to that smaller_int and check against the biggest integer for true conditions by these two

(bigger_int % counter = 0)

and

(smaller_int % counter = 0)

then this counter is already the highest common factor and you can exit the loop.

See this for a loop

http://msdn.microsoft.com/en-us/library/ms178642.aspx

---

Many Thanks & Best Regards, Hua Min

Answer 2

You can use this get the fraction:

CREATE FUNCTION dbo.udf_ConvertToFraction
 (
         @NumToConvert DECIMAL(25, 10)
 )
 RETURNS VARCHAR(75) AS
 BEGIN  

 DECLARE @output VARCHAR(75)
 DECLARE @wholenumber INT
 DECLARE @DECIMAL DECIMAL (25, 10)
 DECLARE @num INT
 DECLARE @denom INT
 DECLARE @multiple INT  

 SET @wholenumber = CAST(@NumToConvert AS INT)
 SET @decimal = @NumToConvert - @wholenumber
 SET @multiple =
                                CAST(
                                       '1' + REPLICATE('0',LEN(CAST(CAST(REVERSE(SUBSTRING(CAST(@decimal AS VARCHAR),
                                       CHARINDEX('.',CAST(@decimal AS VARCHAR))+1, LEN(CAST(@decimal AS VARCHAR)))) AS INT) AS VARCHAR(10))))
                                AS INT)  

 SET @num = @multiple * @decimal
 SET @denom = @multiple  

 IF @num > 0
 BEGIN
         --calculate the greatest common factor
         --AS long AS both numbers are even numbers, keep reducing them.  

         WHILE ((@num % 2) + (@denom % 2)) = 0
         BEGIN
         SET @denom = @denom / 2       
         SET @num = @num / 2
         END  

         --continue reducing numerator and denominator until one
         --is no longer evenly divisible by 5
         WHILE ((@num % 5) + (@denom % 5)) = 0
         BEGIN
         SET @denom = @denom / 5
         SET @num = @num / 5
         END  

 SET @output = CASE WHEN @wholenumber > 0 THEN CONVERT(VARCHAR, @wholenumber) ELSE '' END + ' ' + CONVERT(VARCHAR, @num) + '/' + CONVERT(VARCHAR, @denom)   

 END
 ELSE
 BEGIN
         SET @output = @wholenumber
 END  

 RETURN (@output)
 END
 go

Check more at: Convert numbers to fractions using SQL Server

Answer 3

The lowest common factor for two integers is 1. When you're looking of a prime factorisation of an integer, then this is pretty hard to do. Especially in T-SQL when we consider performance. But you may take a look at one of Celko's articles.

Well, and what do you exactly mean by "convert decimals into fractions"?

Answer 4

The Lowest Common Factor of any two integers is 1.  I assume you mean either Greatest Common Divisor or Least Common Multiple, both are easily computed with a recursive cte, for example to find the GCD and LCM of 24 and 420

Declare @Nbr1 int;
Declare @Nbr2 int;
Set @Nbr1 = 24;
Set @Nbr2 = 420;
;With cte As
(Select @Nbr1 As N1, @Nbr2 As N2, @Nbr1%@Nbr2 As N3
Union All
Select N2, N3, N1%N2 From cte Where N3 > 0)
Select N2 As GCD, @Nbr1*@Nbr2/N2 As LCM From cte Where N3 = 0;

Tom

Answer 5

I would like to convert my decimals into fractions or find the Lowest Common Factor for two intergers. Besides using a function is there any code that I can write.

Fractions are a display issue; we would tend to do this with a look-up table over the range you need. If you want to have some fun, look up Egyptian fractions for a programming exercise.  

Did you mean Greatest Common Factor? The lowest one is 1 :) The clasic answer is Euclid's algorithm

The recursive function is beautiful. Here is skeleton. 

CREATE FUNCTION GCD (a INTEGER, b INTEGER)
AS 
RETURN (
 CASE WHEN b=0 THEN a
      ELSE GCD(b, a%b) END);

---

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

Answer 6

I stand correct. It should be the least common multiple or the common factor between two numbers.  In Tom Cooper code, he has 12 and 420 as your two intergers. 24 and 420 share the common factor of 12. I apologize for the confusion.

@ Tom,

Your code works, but it return  GCD = 12 and the LCM = 840.  I m not sure if the LCM is correct.

Answer 7

LCM = 840 is the correct one for numbers 24 and 420 as 840 is the minimum value that gets divisible by both 24 and 420. Thanks!

Answer 8

@ Deepak

Now I am tracking! It makes since now. The last hurdle that I have is a error code "The Declare SQL construct or statement in not supported." I am using SSRS 2008

Answer 9

@ Deepak

Now I am tracking! It makes since now. The last hurdle that I have is a error code "The Declare SQL construct or statement in not supported." I am using SSRS 2008

Answer 10

@sergent_time: This is imho the closest what you can get for a caclulated value. But keep in mind, that it cannot handle fractions like 1/3 converted to decimal and back very well. Otherwise a look-up table as Joe wrote is necessary.

Answer 11

The two numbers need to be passed as parameters and the above can be either inline table valued function or a stored procedure you call from SSRS.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog