Cast as numeric when field isnumeric

user_3748833 0
Viewed 3811

I am using a view with a column that contains a varchar which can have a value like 8.60 or V or NULL.

And I am trying to create a select statement which casts the value as numeric when it is possible or else return the original value.

So I came up with this test query:

SELECT 
CASE 
    WHEN ISNUMERIC('V') = 1
        THEN cast(round('V', 1) as numeric(4,1))
        ELSE 'V'
    END
    as Value;

And I expect it to return V, but I am getting an error: Error converting data type varchar to numeric. I have tried playing around with TRY_PARSE and TRY_CAST and with the round and cast with no success. The error seems to be with the THEN clause, because without, it does not throw an error.

How should I write this query?

SQL version: Microsoft SQL Server 2014 (SP2-GDR) (KB4019093) - 12.0.5207.0 (X64)

Using: Microsoft SQL Server Management Studio v12.0.2000.8

sql-server-2014 numbers sql-server sql
2 Answers

A case expression returns a single value. That value has a single type. Your case expression is returning both a number and a string. The number takes precedence, so SQL Server attempts to convert the string to a number.

Hence, your error.

You could fix this by dispensing with the 'V' and just using NULL:

SELECT (CASE WHEN ISNUMERIC('V') = 1
             THEN cast(round('V', 1) as numeric(4,1))
             ELSE NULL
        END) as Value;

Or, more simply:

SELECT TRY_CONVERT(numeric(4, 1), 'V')

Note: I assume that you intend 'V' as a sample string value and not as a column. If a column, the single quotes are just wrong.

user_11440350

A case expression cannot return multiple data types. SQL Server has to convert results from all branches to one data type. And according to datatype precedence rules numeric has a higher priority than varchar so it converts v to numeric nevertheless. You could convert the numeric value to varchar though.

Having said that, I discourage using ISNUMERIC because it returns true for nonsense values such as $ and -. Use TRY_CAST as suggested in the other answer:

SELECT CASE
    WHEN TRY_CAST(v AS NUMERIC(4, 1)) IS NOT NULL THEN
        CAST(CAST(v AS NUMERIC(4, 1)) AS VARCHAR(100)) -- cast will round as well
    ELSE v
END
user_870150
Related