I've got a field in this database that stores number like so:
I'm trying to convert these to a full decimal so I can add them up. I've been trying to do this by extracting the fraction out of the number, converting that to a decimal and then adding that to the whole number. I've been having some major problems extracting the numerator. The denominator has been extracted using:
CASE WHEN CHARINDEX('/',Fraction) > 0 THEN RIGHT(Faction,(LEN(Fraction) - (CHARINDEX('/',Fraction)))) ELSE NULL END AS [Denominator]
I'm not sure if that's the most efficient way to do it but it seems to work.
I've tried to pull the numerator out using this method:
CASE WHEN Charindex('/', Fraction) > 0 THEN LEFT(RIGHT(Fraction, Len(Fraction) - Charindex(' ',Fraction)), CHARINDEX('/',Fraction) - 1) ELSE NULL END AS [Numerator]
But that just gives me the fraction itself. I'm not sure if SQL has a problem with a RIGHT inside a LEFT since it doesn't seem to do anything, or, more likely, my code is messed up.
Anyone have any ideas?
Thanks!
You almost got it right: you are calculating the length that you pass to LEFT incorrectly. You need to take the position of space into account.
Instead of
CHARINDEX('/',Fraction) - 1
the length should be
CHARINDEX('/',Fraction) - CHARINDEX(' ',Fraction) - 1
The overall expression should look like this:
CASE WHEN Charindex('/', Fraction) > 0 THEN LEFT(RIGHT(Fraction, Len(Fraction) - Charindex(' ',Fraction)), CHARINDEX('/',Fraction) - CHARINDEX(' ',Fraction) - 1) ELSE NULL END AS [Numerator]