Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 1063

How to convet fraction to decimal

How to convert fraction to decimal using tsql for example
select   myfunction(25 1/8)
select   myfunction(25 1/16)

should return as 25.125
should return as 25.0625
0
Bharat Guru
1 Solution

Commented:
Could you show the syntax of your function?  You could be receiving 0 when doing 1/8 because SQL server treats them as ints and the result as int that would be 0.  If you would indicate that one of them is float then result would be float as well.

For Example:
select convert(float,1)/8
declare @float float
set @float = 8
select 1/@float
set @float = 1
select @float/8
0

Commented:
Give this a try.
Btw, never use float. Float is not precise. Use decimal instead. (I've used 9 decimals but adjust it to your convenience)

``````create function dbo.fractodecimal(@frac varchar(40))
returns decimal(10,9)
as
declare @retnumber decimal(10,9)
declare @frac_tmp varchar(50)

set @retnumber = convert(decimal(10,9), left(@frac, charindex(' ', @frac)))
set @frac_tmp = right(@frac, charindex(' ', reverse(@frac)) - 1)

select 	@retnumber = @retnumber + convert(decimal(10,9), left(@frac_tmp,charindex('/',@frac_tmp)-1)) /
convert(decimal(10,9),right(@fract_tmp, charindex('/', reverse(@frac_tmp)) - 1))

return(@retnumber)

go;

--and you call it like this:

select dbo.fractodecimal('2 1/2')
``````
0

Commented:
Hello patel100,

Here is my take on it.  It returns a float, and ralmada's advice regarding float should be considered.

CREATE FUNCTION dbo.myfunction (@StringIn varchar(100))
RETURNS float
AS BEGIN
DECLARE @IntegerPart float, @FracPart float, @Fraction varchar(100), @Numerator float,
IF CHARINDEX(' ', @StringIn) = 0
ELSE BEGIN
SET @IntegerPart = CONVERT(float, LEFT(@StringIn, CHARINDEX(' ', @StringIn) - 1))
SET @Fraction = SUBSTRING(@StringIn, CHARINDEX(' ', @StringIn) + 1, 100)
SET @Numerator = CONVERT(float, LEFT(@Fraction, CHARINDEX('/', @Fraction) - 1))
SET @Denominator = CONVERT(float, SUBSTRING(@Fraction, CHARINDEX('/', @Fraction) + 1, 100))
SET @Answer = @IntegerPart + @Numerator / @Denominator
END
END

Regards,

Patrick
0

Commented:
0

Commented:
Actually one minor correction on my function:
``````create function dbo.fractodecimal(@frac varchar(40))
returns decimal(15,5)
as
declare @retnumber decimal(15,5)
declare @frac_tmp varchar(50)

set @retnumber = convert(decimal(15,5), left(@frac, charindex(' ', @frac)))
set @frac_tmp = right(@frac, charindex(' ', reverse(@frac)) - 1)

select 	@retnumber = @retnumber + convert(decimal(15,5), left(@frac_tmp,charindex('/',@frac_tmp)-1)) /
convert(decimal(15,5),right(@fract_tmp, charindex('/', reverse(@frac_tmp)) - 1))

return(@retnumber)

go;

--and you call it like this:

select dbo.fractodecimal('2 1/2')
``````
0

Featured Post

Tackle projects and never again get stuck behind a technical roadblock.