Convert fractional string to decimal

Question

I've got a few columns that have values either in fractional strings (i.e. 6 11/32) or as decimals (1.5). Is there a CAST or CONVERT call that can convert these to consistently be decimals?

The error:

Msg 8114, Level 16, State 5, Line 1 Error converting data type varchar to numeric.

Can I avoid doing any kind of parsing?

Thanks!

P.S. I'm working in SQL Server Management Studio 2012.

score 5 · Accepted Answer · answered Nov 20 '14 at 23:58

CREATE FUNCTION ufn_ConvertToNumber(@STR VARCHAR(50))
RETURNS decimal(18,10)
AS
BEGIN
        DECLARE @L VARCHAR(50) = ''
        DECLARE @A DECIMAL(18,10) = 0
        SET @STR = LTRIM(RTRIM(@STR)); -- Remove extra spaces
        IF ISNUMERIC(@STR) > 0 SET @A = CONVERT(DECIMAL(18,10), @STR) -- Check to see if already real number
        IF CHARINDEX(' ',@STR,0) > 0
        BEGIN
            SET @L = SUBSTRING(@STR,1,CHARINDEX(' ',@STR,0) - 1 )
            SET @STR = SUBSTRING(@STR,CHARINDEX(' ',@STR,0) + 1 ,50 )
            SET @A = CONVERT(DECIMAL(18,10), @L)
        END
        IF CHARINDEX('/',@STR,0) > 0
        BEGIN
            SET @L = SUBSTRING(@STR,1,CHARINDEX('/',@STR,0) - 1 )
            SET @STR = SUBSTRING(@STR,CHARINDEX('/',@STR,0) + 1 ,50 )
            SET @A =  @A + ( CONVERT(DECIMAL(18,10), @L) / CONVERT(DECIMAL(18,10), @STR)  )
        END
        RETURN @A
END
GO

Then access it via select dbo.ufn_ConvertToNumber ('5 9/5')

A bit more robust than mine. +1 – Ross Presser Nov 21 '14 at 00:10 — Ross Presser, Nov 21 '14 at 00:10

score 1 · Answer 2 · answered Nov 20 '14 at 23:36

You'll need to parse. As Niels says, it's not really a good idea; but it can be done fairly simply with a T-SQL scalar function.

CREATE FUNCTION dbo.FracToDec ( @frac VARCHAR(100) )
RETURNS DECIMAL(14, 6)
AS
    BEGIN
        RETURN CASE 
            WHEN @frac LIKE '% %/%'
                THEN CAST(LEFT(@frac, CHARINDEX(' ', @frac, 1) -1) AS DECIMAL(14,6)) + 
                    ( CAST(SUBSTRING(@frac, CHARINDEX(' ', @frac, 1) + 1, CHARINDEX('/', @frac, 1)-CHARINDEX(' ',@frac,1)-1) AS DECIMAL(14,6)) 
                    / CAST(RIGHT(@frac, LEN(@frac) - CHARINDEX('/', @frac, 1)) AS DECIMAL(14,6)) )
            WHEN @frac LIKE '%/%'
                THEN CAST(LEFT(@frac, CHARINDEX('/', @frac, 1) - 1) AS DECIMAL(14,6)) / CAST(RIGHT(@frac, LEN(@frac) - CHARINDEX('/', @frac, 1)) AS DECIMAL(14,6))
            ELSE 
                CAST(@frac AS DECIMAL(14,6)) 
            END
    END
GO

-- Test cases
SELECT  dbo.FracToDec('22/7'), dbo.fracToDec('3.117'), dbo.fracToDec('7 3/4')

-- Output
-- 3.142857   3.117000   7.750000

Note that this will fail if the contents passed does not actually match the forms "mm/nn", "xx mm/nn" or a real decimal.

score 1 · Answer 3 · edited Nov 23 '14 at 16:31

And here is the solution without functions and stored procedures - just for the fun of it. First you have to create new column (I call it decimal) and then fill it with the values converted from the original mixed-format column (called inconsistent) using the following query:

UPDATE  "my_table"
SET     "decimals" = CASE WHEN CHARINDEX('/', "inconsistent") > 0
                          THEN CAST(CASE WHEN CHARINDEX(' ',
                                                        RTRIM(LTRIM("inconsistent"))) > 0
                                         THEN LEFT(RTRIM(LTRIM("inconsistent")),
                                                   CHARINDEX(' ',
                                                             RTRIM(LTRIM("inconsistent")))
                                                   - 1)
                                         ELSE '0'
                                    END AS FLOAT)
                               + CAST(SUBSTRING(RTRIM(LTRIM("inconsistent")),
                                                CHARINDEX(' ',
                                                          RTRIM(LTRIM("inconsistent")))
                                                + 1,
                                                CHARINDEX('/',
                                                          RTRIM(LTRIM("inconsistent")))
                                                - 1 - CHARINDEX(' ',
                                                              RTRIM(LTRIM("inconsistent")))) AS FLOAT)
                               / CAST(RIGHT(RTRIM(LTRIM("inconsistent")),
                                            LEN(RTRIM(LTRIM("inconsistent")))
                                            - CHARINDEX('/',
                                                        RTRIM(LTRIM("inconsistent")))) AS FLOAT)
                          ELSE CAST(RTRIM(LTRIM("inconsistent")) AS FLOAT)
                     END

And I actually was thinking someone would find it elegant :)) Oh, cruel world! :)) Jokes apart, thanks of course! As to the backticks: got used to MySQL (in PhpMyAdmin to be exact) needing those to deal with database/table/column names correctly (not sure if that worked w/o them - probably it would - but I got that habit somehow). Do realize the question is about MS SQL, but old habits die hard (plus should still work). Some story here for ya :) — striving_coder, Nov 23 '14 at 03:52
Actually the backquotes do *not* work in MS SQL, just tried. Either "double quotes" or [brackets]. And MS SQL has no `IF()` construct; it's `CASE WHEN cond THEN val1 ELSE val2 END`. Sorry :( — Ross Presser, Nov 23 '14 at 16:04
@RossPresser: Thanks for the note on MS SQL vs. MySQL syntax and for reformatting/updating syntax! — striving_coder, Nov 23 '14 at 16:33

score 0 · Answer 4 · answered Nov 20 '14 at 23:18

0

I am not aware of any database system, or code framework for that matter, supporting strings like 6 11/32 natively. Your best bet is to add a column to the relevant table and denormalize the actual value in there with a script, or creating a view on top of it that does that automatically. It'll take some complex code though, and it's probably not a good idea to do it in SQL at all.

answered Nov 20 '14 at 23:18

Niels Keurentjes

41,402
9
98
136

Excel supports fractions :) – D Stanley Nov 20 '14 at 23:35
Although I have once seem some dramatically bad web applications use Excel as their data layer, I wouldn't exactly call it a 'database system' ;) Excel is an end-user tool, thus it makes more sense to support end-user notations. – Niels Keurentjes Nov 20 '14 at 23:38
A Fraction class is often given as an exercise in beginning OOP programming classes. It's pretty easy to do, and can easily be found by searching for "C# Fraction class" or "Java Fraction class". – Ross Presser Nov 20 '14 at 23:38
@RossPresser I didn't put the word 'natively' at the end of my first sentence without consideration. It's trivial code, but so rarely used in real world code that it doesn't make sense to include stock implementations for them in frameworks or databases. – Niels Keurentjes Nov 20 '14 at 23:39

Convert fractional string to decimal

4 Answers4

Linked