0

There are plenty of answers to this question, but mostly in other languages like C#, VB, Python, etc.

What I want is a way to expand my SQL code below, to normalise my results to the lowest common-denominator.

DECLARE @VALUE AS sql_variant
SET @VALUE = 0.5

DECLARE @SCALE AS INT
SET @SCALE = CONVERT(INT, SQL_VARIANT_PROPERTY(@VALUE, 'SCALE'))

DECLARE @2POWER AS INT
SET @2POWER = POWER(10,@SCALE)

DECLARE @WHOLE AS INT
SET @WHOLE = CONVERT(INT, @VALUE)

DECLARE @DECIMALS AS NUMERIC
SET @DECIMALS = (CONVERT(DECIMAL(18,10), @VALUE) % 1) * @2POWER

SELECT 
    CASE WHEN @WHOLE = 0 THEN '= ' 
          ELSE '= ' + CAST(@WHOLE AS VARCHAR(10)) + ' '
    END + 
    CAST(@DECIMALS AS VARCHAR(10)) + 
    ' / ' + CAST(@2POWER AS VARCHAR(10))

This returns "= 5/10" which is correct, but how can I use an algorithm like this, to return = 1/2 instead?

If I change the value above to 0.3333 I would like to see "1/3", instead of "3333 / 10000".

Fandango68
  • 3,956
  • 3
  • 35
  • 60
  • SQL is not the best language to process information. SQL is geared towards storing and retrieving data with high performance and reliability. – The Impaler Jul 21 '21 at 00:28
  • @TheImpaler - in my case it *has* to be in SQL :) – Fandango68 Jul 21 '21 at 00:36
  • 1
    1. 3333 / 10000 is not the same as 1/3. There are an INFINITE number of rational numbers that fit between 3333 / 10000 and 1 / 3. Which one is the "right" one? 2. The best known algorithm for GCD is Euclid's algorithm which is iterative. SQL does not do iterative in its column calcs. – Chris Maurer Jul 21 '21 at 02:58

0 Answers0