kevin.lambert
New Member

11 Jan 2008 05: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 = 31/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

11 Jan 2008 06: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 31/3, or would that be rejected since it's "rounded"?





kevin.lambert
New Member

11 Jan 2008 06: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 31/3, or would that be rejected since it's "rounded"?
Ideally 3.33 would be converted to 333/100. I suppose for the sake of performance the input value could be rounded. 




nosepicker
New Member

11 Jan 2008 08:08 AM 

Here's what I came up with. I thought it was easier to keep 3.33 as 333/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

11 Jan 2008 09: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 51/8 but instead I get 513/100. The value 10.6875 should convert to 1011/16, but I get 1069/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

11 Jan 2008 11:49 AM 

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

23 Jan 2008 09: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

23 Jan 2009 09: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 fubars. *waving hello* 




nosepicker
New Member

11 Feb 2009 09:50 AM 

Good catch  thanks for the tidying. (greetings and salutations)




