Data Management

Convert numbers to fractions using SQL Server

DBA Tim Chapman shows how to write a TSQL user-defined function to convert a floating point decimal number into a fractional representation of the same number.

I recently worked on an interesting problem that involved converting a decimal number to a fraction value in SQL Server. At first glance, it seemed reasonably simple, but after digging into the actual code a bit, I realized it was rather challenging.

Here's how you can write a TSQL user-defined function to convert a floating point decimal number into a fractional representation of the same number.

Converting numbers

Converting numbers to or from string values in SQL Server is fairly simple. You can typically use the built-in CAST or CONVERT functions to do the conversions for you. When converting from a decimal value to a string representation of the fraction of that value, it becomes a bit more complex.

For example, if you need to convert the decimal 5.25 to the fraction 5 ¼, where do you start to do the conversion? The mathematical rule is to multiply .25 by 100, which gives you the fraction 25/100. However, you then need to put the fraction into its lowest terms. This becomes increasingly complicated when presented with more complex decimals, such as 5.234327.

The function

Below is the script for the user-defined function that accepts a decimal value and converts that number to its fractional representation.

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

The first thing the udf_ConvertToFraction function does is store the input value as separate whole number and decimal number values. Next, I need to determine the multiple to use for my numerator and denominator. This value is calculated by determining the number of decimal places to the right of the decimal point.

The next step is to reduce the fraction to its lowest terms. To do this, I use the modulo operator (%) to determine the remainder after the division. First, if the numerator and denomiator are even numbers, I continue dividing them by 2 until they are reduced as far as they can be, or one of them is no longer even. I repeat the same step, except this time I check to see if both numbers are evenly divisible by 5. Once these calculations are complete, I am done and can construct the final fractional value.

To call my new function, I can execute the following script:

SELECT dbo.udf_ConvertToFraction('5.234')

My resulting fraction is 5 117/500.

Looks can be deceiving

Although the function in this article isn't very complicated, it shows that sometimes solutions are more complex than we think. This is true with a lot of computing problems. So take your time and evaluate the problem before you let others know how easy or hard it will be to solve.

Tim Chapman a SQL Server database administrator and consultant who works for a bank in Louisville, KY. Tim has more than eight years of IT experience, and he is a Microsoft certified Database Developer and Administrator. If you would like to contact Tim, please e-mail him at chapman.tim@gmail.com.

About

Tim Chapman is a SQL Server MVP, a database architect, and an administrator who works as an independent consultant in Raleigh, NC, and has more than nine years of IT experience.

7 comments
ZeroGhost
ZeroGhost

How do I convert decimal to integer in sql server 2000?

pdd
pdd

Doesn't give good results for fractions such as 1/6 small mockup does this, need small modification to subtract the whole number declare @target DECIMAL(30,10) declare @nominator DECIMAL(30,10) declare @denominator DECIMAL(30,10) declare @attempt DECIMAL(30,10) declare @min_difference DECIMAL(30,10) SELECT @target = 0.16666, @min_difference = 0.00001, @nominator = 1, @denominator = 1 while @target - (@nominator / @denominator) NOT BETWEEN -1 * @min_difference AND @min_difference begin PRINT 'denominator : ' + CONVERT(VARCHAR,@denominator) SELECT @denominator = @denominator + 1, @nominator = 1 while @nominator

StuManCA
StuManCA

Tim, Fantastic! Most of my MS SQL Servers are binary sort order for our application. I scratched my head for a minute from the error message on creating the function till I realized, your DECLARE @DECIMAL DECIMAL (25, 10) needs to be a DECLARE @decimal DECIMAL (25, 10) for case sensitivity to match the usages of the @decimal variable. This is really cool. Thanks for sharing this with us!!

ccurbina
ccurbina

This is a good start. I'm going to modify it so that it returns the best answer when given .33333 or .16666, etc, you know, the repeating decimals.

BALTHOR
BALTHOR

Strut your stuff.Smoke that CPU!Show us your program running.

JackOfAllTech
JackOfAllTech

A long time ago, I wrote functions to do long arithmetic on VERY large numbers just to see if I could. When I casually mentioned it to a friend in another dept., he was ecstatic. He had been searching for programs to do that for months (I don't remember why). It just goes to show you that solving problems always has benefits.

Editor's Picks