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 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.
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
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 =
'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))))
SET @num = @multiple * @decimal
SET @denom = @multiple
IF @num > 0
—calculate the greatest common factor
—AS long AS both numbers are even numbers, keep reducing them.
WHILE ((@num % 2) + (@denom % 2)) = 0
SET @denom = @denom / 2
SET @num = @num / 2
—continue reducing numerator and denominator until one
—is no longer evenly divisible by 5
WHILE ((@num % 5) + (@denom % 5)) = 0
SET @denom = @denom / 5
SET @num = @num / 5
SET @output = CASE WHEN @wholenumber > 0 THEN CONVERT(VARCHAR, @wholenumber) ELSE '' END + ' ' + CONVERT(VARCHAR, @num) + '/' + CONVERT(VARCHAR, @denom)
SET @output = @wholenumber
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:
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 firstname.lastname@example.org.
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.