CODEWITHSUNDEEP
sqlsql-servercasting
tmwoods
tmwoods

Reputation: 2413

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

Upvotes: 0

Views: 6405

Answers (4)

striving_coder
striving_coder

Reputation: 798

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

Upvotes: 1

Stephen Bodine
Stephen Bodine

Reputation: 519

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

Upvotes: 5

Ross Presser
Ross Presser

Reputation: 6255

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.

Upvotes: 1

Niels Keurentjes
Niels Keurentjes

Reputation: 41958

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.

Upvotes: 0

Related Questions