Fractions and how to do T-SQL math with them

Posted on 2003-03-17
Medium Priority
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?


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

I want sql server to return 2 naturally.

Question by:tca245
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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.

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

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

Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

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.


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)

LVL 34

Expert Comment

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


Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Suggested Courses

777 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