Convert decima to fraction

# Convert decima to fraction

• Wednesday, August 15, 2012 2:10 PM

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.

### All Replies

• Wednesday, August 15, 2012 2:21 PM

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

• Wednesday, August 15, 2012 2:22 PM

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

• Wednesday, August 15, 2012 2:24 PM

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"?

• Wednesday, August 15, 2012 2:34 PM

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

• Wednesday, August 15, 2012 3:48 PM

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

• Thursday, August 16, 2012 1:37 PM

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.

• Thursday, August 16, 2012 1:45 PM

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!
• Thursday, August 16, 2012 2:05 PM

@ 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

• Thursday, August 16, 2012 2:05 PM

@ 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

• Friday, August 17, 2012 5:00 PM

@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.
• Saturday, August 18, 2012 9:36 PM
Moderator

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