function to return fraction from decimal and rounds to the nearest 16th

Question

function to return fraction from decimal and rounds to the nearest 16th

rosarozina

SSC-Addicted

Points: 484
More actions
June 16, 2017 at 11:33 am

#322060

I want to convert a decimal number to a fraction and round to the nearest 16th.
the fraction should be like 2 1/2 instead of 9/4.
what is the appropriate sql code?
I use this and it does not work.
CREATE FUNCTION dbo.ufnConvertToFraction
(
   @NumbertoConvert FLOAT
)
RETURNS VARCHAR(20) AS
BEGIN
DECLARE
@output VARCHAR(20)
, @wholenumber INT
, @DECIMAL DECIMAL (25, 10)
, @num float
, @denom INT
, @multiple INT
SET @wholenumber = CAST(@NumbertoConvert AS INT)
SET @decimal = @NumbertoConvert - @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(20))))
           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
---- Round to the nearest 16th
RETURN cast(round(@output/16,0)*16 as int)
END

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183698 More actions · Answer 1

As you're only approaching to the nearest 16th, I'm using a bit of hard-coding. I've also changed the function into an In-Line Table-Valued Function. These functions are extremely fast and won't suffer from the performance hits of scalar functions. At the end of the code, there's an example on how to use it (it uses a table-value constructor, but you should use your table).
CREATE FUNCTION dbo.iConvertToFraction( @Decimal decimal(18,8) ) RETURNS TABLE WITH SCHEMABINDING AS RETURN SELECT CAST(FLOOR(@Decimal) AS varchar(20)) + fraction AS FractionNum FROM (VALUES(0 , '') , (1 , ' 1/16') , (2 , ' 1/8'), (3 , ' 3/16'), (4 , ' 1/4'), (5 , ' 5/16') , (6 , ' 3/8'), (7 , ' 7/16'), (8 , ' 1/2'), (9 , ' 9/16') , (10, ' 5/8'), (11, ' 11/16'), (12, ' 3/4'), (13, ' 13/16'), (14, ' 7/8'), (15, ' 15/16'))x(n,fraction) WHERE ROUND((@Decimal- FLOOR(@Decimal))*16,0)/16 = n/16. GO

SELECT * FROM (VALUES(5),(2.5),(3.47),(1.75),(pi()))x(num) CROSS APPLY dbo.iConvertToFraction( x.num) f

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Sean Lange SSC Guru Points: 286573 More actions · Answer 2

For starters lets format this function so we can read it.

CREATE FUNCTION dbo.ufnConvertToFraction ( @NumbertoConvert FLOAT ) RETURNS VARCHAR(20) AS

BEGIN

DECLARE @output VARCHAR(20) , @wholenumber INT , @decimal DECIMAL (25, 10) , @num float , @denom INT , @multiple INT

SET @wholenumber = CAST(@NumbertoConvert AS INT) SET @decimal = @NumbertoConvert - @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(20))))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

---- Round to the nearest 16th RETURN cast(round(@output/16,0)*16 as int)

END

Now what I am having a hard time figuring out is what this thing does. And not really sure why you have a mix of decimal and floats. Can you explain what you mean by you should get 2 1/2 and not 9/4? What is the input and what you are expecting at output?

Please see the first link in my signature for best practices when posting questions.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Sean Lange SSC Guru Points: 286573 More actions · Answer 3

Holy cow Luis. I was not at all understanding what they were looking for. I was not even on the same planet as your post. That is pretty awesome my friend.