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
  • Last Modified:

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
Asked:
Bharat Guru
1 Solution
 
Igor-KCommented:
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
 
ralmadaCommented:
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')

Open in new window

0
 
Patrick MatthewsCommented:
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,
            @Denominator float, @Answer float
      IF CHARINDEX(' ', @StringIn) = 0
            SET @Answer = CONVERT(float, @StringIn)
      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
      RETURN @Answer
END

Regards,

Patrick
0
 
ralmadaCommented:
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')

Open in new window

0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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