# Format decimal value to fraction using a tally table.

,

This function takes a decimal value and returns fractional representation of this value.

SELECT dbo.DecimalToFraction(12.5)

Result: 12 1/2

The interesting thing about this code is that it uses a tally table to find the smallest denominator for the decimal part.

```-- Raw Code

DECLARE @Value AS DECIMAL(10,3)
SET @Value = 10.375

SELECT Fraction = CONVERT(VARCHAR,p.IntegerPart)+
CASE WHEN f.Denominator IS NOT NULL
THEN ' '+CONVERT(VARCHAR,Numerator)+'/'+CONVERT(VARCHAR,Denominator)
ELSE ''
END
FROM (SELECT IntegerPart =FLOOR(@Value) , DecimalPart = @Value - FLOOR(@Value) ) p
OUTER APPLY(SELECT TOP 1
Denominator = Number,
Numerator = CONVERT(INT, DecimalPart  / (1.0/Number) )
FROM Numbers
WHERE DecimalPart > 0 AND
Number BETWEEN 2 AND 200 AND
DecimalPart  % (1.0 / Number) = 0.0
ORDER BY Denominator ASC
) f

GO

--  Wrapped in a function
CREATE FUNCTION dbo.DecimalToFraction( @Value AS DECIMAL(30,10) )
RETURNS VARCHAR(50)
AS
BEGIN

DECLARE @FractionNumber AS VARCHAR(50)

SELECT @FractionNumber = CONVERT(VARCHAR,p.IntegerPart)+
CASE WHEN f.Denominator IS NOT NULL
THEN ' '+CONVERT(VARCHAR,Numerator)+'/'+CONVERT(VARCHAR,Denominator)
ELSE ''
END
FROM (SELECT IntegerPart =FLOOR(@Value) , DecimalPart = @Value - FLOOR(@Value) ) p
OUTER APPLY(SELECT TOP 1
Denominator = Number,
Numerator = CONVERT(INT, DecimalPart  / (1.0/Number) )
FROM Numbers
WHERE DecimalPart > 0 AND
Number BETWEEN 2 AND 200 AND
DecimalPart  % (1.0 / Number) = 0.0
ORDER BY Denominator ASC
) f

RETURN @FractionNumber
END```

## Rate

1.67 (3)

You rated this post out of 5. Change rating

## Rate

1.67 (3)

You rated this post out of 5. Change rating