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
6
Medium Priority
?
1,838 Views
Last Modified: 2010-08-05
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
Comment
Question by:tca245
  • 5
6 Comments
 
LVL 34

Expert Comment

by:arbert
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

by:arbert
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

by:
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 34

Expert Comment

by:arbert
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

by:tca245
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

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

Brett
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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

571 members asked questions and received personalized solutions in the past 7 days.

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

Join & Ask a Question