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

x
Solved

# Fractions and how to do T-SQL math with them

Posted on 2003-03-17
Medium Priority
1,838 Views
I have a nvarchar(10) string with a value of '2/3'

How do I turn that into a fraction and apply it to a value to calculate the percentage result?

Example,

Declare @frac nvarchar(10)
set @frac = '2/3'
select 6*@frac

I want sql server to return 2 naturally.

0
Question by:tca245
• 5

LVL 34

Expert Comment

ID: 8156082
Do you only want this to work with division, or any arithmetic?

I have a solution, but the problem is that SQL Server looks at 2/3 and returns 0 because it's trying to do integer division.  However, I have a solution, I just need to know if you want it to apply to other arithmetic operations.

Brett
0

LVL 34

Expert Comment

ID: 8156165
Not glamorous, but here you go:

declare @frac nvarchar(20)
set @frac='2/3'

select convert(decimal(10,9),left(charindex("/",@frac,1),3)) / convert(decimal(10,9),substring(@frac,charindex("/",@frac,1)+1,4))

Brett
0

LVL 34

Accepted Solution

arbert earned 200 total points
ID: 8156187
Oops, little error in the last one:

declare @frac nvarchar(20)
set @frac='2/2'

select convert(decimal(10,9),left(@frac,charindex("/",@frac,1)-1)) / convert(decimal(10,9),substring(@frac,charindex("/",@frac,1)+1,4))

Brett
0

LVL 34

Expert Comment

ID: 8156197
One other thing, change the decimal(??,?) to match your specific case--depending on how much precision you need and the size of the fractions you input.

brett
0

Author Comment

ID: 8156356
Beautiful Example!

I have modifed it to allow multiple placeholders in the numerator and denominator:

declare @frac nvarchar(20)
declare @numerator decimal
declare @denominator decimal

set @frac='7/22'
set @numerator = convert(decimal,left(@frac,charindex('/',@frac)-1))
set @denominator = convert(decimal,substring(@frac,charindex('/',@frac)+1,Len(@frac)-charindex('/',@frac)))

select 6*(@numerator/@denominator)

0

LVL 34

Expert Comment

ID: 8156387
I wouldn't say beautiful, but it gave you a starting point :)

Brett
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
###### Suggested Courses
Course of the Month10 days, 22 hours left to enroll