Convert Decimal to Rational (Text)

Last Post 23 Jan 2009 10:46 PM by dianagele. 8 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
kevin.lambert
New Member
New Member

--
11 Jan 2008 06:34 AM
We have a UDF that was developed to convert a decimal value (real, float, etc.) to its text equivalent as a fraction (e.g. 3.25 = 3-1/4). The code for this conversion follows:

CREATE FUNCTION dbo.fx_RealToString (@value float)
RETURNS varchar(100) AS BEGIN
DECLARE @result varchar(100)

SET @result = FLOOR(@value)
IF (FLOOR(@value) <> @value)
BEGIN
SET @value = (@value - FLOOR(@value))

DECLARE @numerator real
DECLARE @denominator real
DECLARE @failed tinyint
SET @numerator = 1
SET @denominator = 2
SET @failed = 0

WHILE (((@numerator / @denominator) <> @value) AND (@failed <> 1))
BEGIN
IF ((@numerator + 1) = @denominator)
BEGIN
SET @numerator = 1
SET @denominator = (@denominator + 1)
END
ELSE SET @numerator = (@numerator + 1)

IF (@denominator > 32) SET @failed = 1
END

SET @result = (@result + '-' + CONVERT(varchar(100), @numerator) + '/' + CONVERT(varchar(100), @denominator))
END

IF (@failed = 1) SET @result = NULL
ELSE
BEGIN
IF (SUBSTRING(@result, 1, 2) = '0-') SET @result = SUBSTRING(@result, 3, LEN(@result) - 2)
END

RETURN (@result)
END

This function is used extensively in our reports and applications. As you can see, we have the function quit if it can't find a rational equivalent smaller than 32nds.

I'm looking to make this function more elegant and to increase its performance. Any suggestions would be greatly appreciated.
nosepicker
New Member
New Member

--
11 Jan 2008 07:37 AM
Did you want to return only exact fractions, or would rounded ones be ok too? In other words, for the decimal 3.33, would you want to return 3-1/3, or would that be rejected since it's "rounded"?
kevin.lambert
New Member
New Member

--
11 Jan 2008 07:57 AM
quote:

Originally posted by: nosepicker
Did you want to return only exact fractions, or would rounded ones be ok too? In other words, for the decimal 3.33, would you want to return 3-1/3, or would that be rejected since it's "rounded"?



Ideally 3.33 would be converted to 3-33/100. I suppose for the sake of performance the input value could be rounded.
nosepicker
New Member
New Member

--
11 Jan 2008 09:08 AM
Here's what I came up with. I thought it was easier to keep 3.33 as 3-33/100:

CREATE FUNCTION dbo.fx_RealToString (@value decimal(25, 2))
RETURNS varchar(100) AS
BEGIN
DECLARE @result varchar(100),
@integer int,
@decimal decimal (2, 2),
@numerator int,
@denominator int

SET @decimal = @value - FLOOR(@value)
SET @integer = FLOOR(@value)

SET @numerator = 100 * @decimal
SET @denominator = 100

WHILE @numerator % 2 = 0 AND @denominator % 2 = 0
BEGIN
SET @numerator = @numerator / 2
SET @denominator = @denominator / 2
END


WHILE @numerator % 5 = 0 AND @denominator % 5 = 0
BEGIN
SET @numerator = @numerator / 5
SET @denominator = @denominator / 5
END


SELECT @result = CONVERT(varchar, @integer) + '-' + CONVERT(varchar, @numerator) + '/' + CONVERT(varchar, @denominator)

RETURN (@result)
END
kevin.lambert
New Member
New Member

--
11 Jan 2008 10:34 AM
Did some testing and here's what I've come up with.

I have a table of text section information that contains a decimal version for width and height. Using my original function and running on my craptop computer it took 6 seconds to convert ~55,000 rows (width and height are separate fields). Using your revised function it took only 1.5 seconds to convert the same data set.

However, your function runs into some trouble with a few values. For example, the value 5.125 should convert to 5-1/8 but instead I get 5-13/100. The value 10.6875 should convert to 10-11/16, but I get 10-69/100.

I'm impressed with the improvement in speed. Just need to work out those issues. It may be because you're assuming 100 as the starting denominator?

-----

The algorithm I'd like to use would be:

A) Take an input value 5.125 and remove the "whole" part, 5 in this case leaving only the rational part, 0.125.
B) Multiple the rational part in a way that results in a new whole number, so 0.125 * 1000 becomes 125.
C) Use the new whole number as the numerator and the multiplier as the denominator for the starting point (125/1000 in this case).

From that point finding the GCF should be trivial. The problem I have is in testing the floating point number to determine that it's a whole number. I have tried converting 0.125 to a VarChar and using the length as an exponent of 10 to get the denominator. That didn't always work as some floating point numbers will have very long repeating values due to their not being accurately represented by the computer.
nosepicker
New Member
New Member

--
11 Jan 2008 12:49 PM
Sorry, you're right - I was assuming a maximum of 2 digits in the decimal part. The function can be easily changed to varying precisions. Here, I've expanded it to a maximum of 5 digits in the decimal part. You can modify it yourself accordingly. Also, I prefer not to use floating point data due to the imprecisions that you have already mentioned:


ALTER FUNCTION dbo.fx_RealToString (@value decimal(25, 5))
RETURNS varchar(100) AS
BEGIN
DECLARE @result varchar(100),
@integer int,
@decimal decimal (5, 5),
@numerator int,
@denominator int

SET @decimal = @value - FLOOR(@value)
SET @integer = FLOOR(@value)

SET @numerator = 100000 * @decimal
SET @denominator = 100000

WHILE @numerator % 2 = 0 AND @denominator % 2 = 0
BEGIN
SET @numerator = @numerator / 2
SET @denominator = @denominator / 2
END


WHILE @numerator % 5 = 0 AND @denominator % 5 = 0
BEGIN
SET @numerator = @numerator / 5
SET @denominator = @denominator / 5
END


SELECT @result = CONVERT(varchar, @integer) + '-' + CONVERT(varchar, @numerator) + '/' + CONVERT(varchar, @denominator)

RETURN (@result)
END

kevin.lambert
New Member
New Member

--
23 Jan 2008 10:48 AM
Thank you! Yes, this does work nicely. With my test data I do not notice a performance difference when working out to more decimal places. This is much faster than my original function.
dianagele
New Member
New Member

--
23 Jan 2009 10:46 PM
If there is nothing in the numerator, the text value would still show a fraction.
I took the liberty & tidied it up a little...
-------------------------------------------------
if @numerator <> 0
begin
select @result = convert(varchar, @integer) + '-' + convert(varchar, @numerator) + '/' + convert(varchar, @denominator)
end

else
begin
select @result = convert(varchar, @integer) -- return the whole number
end

return (@result)
end
---------------------------------------------------------

I remember the infamous nosepicker; he has solved a few of my coding fu-bars. *waving hello*
nosepicker
New Member
New Member

--
11 Feb 2009 10:50 AM
Good catch - thanks for the tidying. (greetings and salutations)


Acceptable Use Policy
---