CODEWITHSUNDEEP
t-sqlsql-server-2012
Chris
Chris

Reputation: 343

Case when statement conversion failed with column that contains integers and string

Below is what my data looks like for the case when statement:

0
1
NULL
Not Obtainable
NULL
Not Obtainable
2
0
NULL
1
NULL
NULL

Here is the query I am trying to use:

SELECT  
    *,
    CASE 
       WHEN ER_MENTAL > 1 THEN 'Yes'
       WHEN ER_MENTAL < 1 THEN 'No'
       WHEN ER_MENTAL = 'Not Obtainable' THEN 'No'
    END AS ER_MENTAL_RESP
FROM 
    #HLQ

This is the error I'm getting:

Conversion failed when converting the varchar value 'Not Obtainable' to data type int.

I've tried various version of CONVERT in front of each case when with no luck

Upvotes: 1

Views: 658

Answers (2)

Paul McLoughlin
Paul McLoughlin

Reputation: 2293

If you need a version that will work on earlier versions of SQL Server then using ISNUMERIC should work:

select er_mental,
    case when ISNUMERIC(er_mental) = 1 then
        case when er_mental > 1 then 'Yes'
             when er_mental < 1 then 'No'
        end
        when er_mental = 'Not obtainable' then 'No'
    end as er_mental_resp
from #hlq;

Upvotes: 1

S3S
S3S

Reputation: 25122

Your column is VARCHAR so you can't compare it to an INT without converting it. You can use TRY_CONVERT since you are using 2012 which will avoid your error. You could also compare it to it's varchar equivalent, like ER_MENTA > '1' but you could run into issues since varchar's aren't compared the same way. i.e. 11 is < 2 when using varchar. select 1 where '11' < '2'

SELECT *,
CASE WHEN TRY_CONVERT(INT, ER_MENTAL) > 1 THEN 'Yes'
WHEN TRY_CONVERT(INT, ER_MENTAL) < 1 THEN 'No'
WHEN ER_MENTAL = 'Not Obtainable' THEN 'No'
END AS ER_MENTAL_RESP
FROM #HLQ

Upvotes: 0

Related Questions