Converting numeric values in varchar to decimal

Question

Error converting numeric values from varchar datatype to decimal

select 
    case PCI1__c 
       when ISNUMERIC(rtrim(ltrim(PCI1__c))) 
          then convert(decimal(10, 2), PCI1__c)
          else null 
    end as PCI1__c 
from 
    MX_CREDIT_ANALYSIS;

Fyi, the column contains values like 'no revenue', 'two', 'revenue22', '5.5'

Getting error:

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

Answer 1

isnumeric() returns an integer, so when you do a CASE pci1__c WHEN isnumeric(...) ... then pci1__c, which is a varchar, gets compared to an integer. In order to be able to do that comparison, the DBMS wants to upcast the varchar to an integer. That fails as (expected) integer representations don't include a decimal point.

Since 2012 try_convert() is available. It'll convert when possible and otherwise return NULL.

SELECT try_convert(decimal(10, 2), pci1__c) pci1__c
       FROM mx_credit_analysis;

Answer 2

If you want to use the CASE, then

Query

select 
    case when ISNUMERIC(rtrim(ltrim([PCI1__c]))) = 1
    then convert(decimal(10, 2), PCI1__c)
    else null end as PCI1__c 
from [MX_CREDIT_ANALYSIS];