For Example:

select convert(float,1)/8

declare @float float

set @float = 8

select 1/@float

set @float = 1

select @float/8

Solved

Posted on 2009-04-22

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

select myfunction(25 1/8)

select myfunction(25 1/16)

should return as 25.125

should return as 25.0625

5 Comments

For Example:

select convert(float,1)/8

declare @float float

set @float = 8

select 1/@float

set @float = 1

select @float/8

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')
```

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

```
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')
```

By clicking you are agreeing to Experts Exchange's Terms of Use.

Title | # Comments | Views | Activity |
---|---|---|---|

Order by but want it in specific order | 2 | 14 | |

SQL Server Linked Server Using Oracle OPS$ Windows Authenticated Account | 7 | 23 | |

SQL Server 2008 Error | 7 | 27 | |

Why is the output of this function is like this? | 4 | 0 |

Join the community of 500,000 technology professionals and ask your questions.

Connect with top rated Experts

**10** Experts available now in Live!