Convert Fractional String To Decimal

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

Solution 1:

CREATEFUNCTION ufn_ConvertToNumber(@STRVARCHAR(50))
RETURNSdecimal(18,10)
ASBEGINDECLARE@LVARCHAR(50) =''DECLARE@ADECIMAL(18,10) =0SET@STR= LTRIM(RTRIM(@STR)); -- Remove extra spaces
        IF ISNUMERIC(@STR) >0SET@A=CONVERT(DECIMAL(18,10), @STR) -- Check to see if already real number
        IF CHARINDEX(' ',@STR,0) >0BEGINSET@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) >0BEGINSET@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)  )
        ENDRETURN@AEND
GO

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

Solution 2:

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.

CREATEFUNCTIONdbo.FracToDec ( @frac VARCHAR(100) )
RETURNSDECIMAL(14, 6)
ASBEGINRETURNCASEWHEN@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.1428573.1170007.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.

Solution 3:

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" =CASEWHEN CHARINDEX('/', "inconsistent") >0THENCAST(CASEWHEN CHARINDEX(' ',
                                                        RTRIM(LTRIM("inconsistent"))) >0THENLEFT(RTRIM(LTRIM("inconsistent")),
                                                   CHARINDEX(' ',
                                                             RTRIM(LTRIM("inconsistent")))
                                                   -1)
                                         ELSE'0'ENDASFLOAT)
                               +CAST(SUBSTRING(RTRIM(LTRIM("inconsistent")),
                                                CHARINDEX(' ',
                                                          RTRIM(LTRIM("inconsistent")))
                                                +1,
                                                CHARINDEX('/',
                                                          RTRIM(LTRIM("inconsistent")))
                                                -1- CHARINDEX(' ',
                                                              RTRIM(LTRIM("inconsistent")))) ASFLOAT)
                               /CAST(RIGHT(RTRIM(LTRIM("inconsistent")),
                                            LEN(RTRIM(LTRIM("inconsistent")))
                                            - CHARINDEX('/',
                                                        RTRIM(LTRIM("inconsistent")))) ASFLOAT)
                          ELSECAST(RTRIM(LTRIM("inconsistent")) ASFLOAT)
                     END

Solution 4:

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.