CODEWITHSUNDEEP
sql-serversql-server-2012
Jacob
Jacob

Reputation: 11

Could not convert varchar to numeric

Query:

Select 
    case 
       when itembar > 1.0 
          then 'yes' 
          else 'no' 
    end 
from 
    table

Error:

Itembar : varchar Error; could not convert varchar to numeric

I tried cast function but it's not working.. please suggest

Upvotes: 1

Views: 472

Answers (2)

TriV
TriV

Reputation: 5148

In SQL Server 2012+, you could use TRY_CAST, TRY_CONVERT, TRY_PARSE to avoid error when convert data. If there is any error, these functions return NULL.

For example could could do

Select case when TRY_CAST(itembar as decimal(10,2)) > 1.0 then 'yes' else 'no' end 
from table

Select case when TRY_CONVERT(decimal(10,2), itembar) > 1.0 then 'yes' else 'no' end 
from table

But as @Gurwinder suggested, if your data is all numeric then change it's type to any numeric type: decimal, int, ....

Upvotes: 3

shishir
shishir

Reputation: 851

Tried to execute your problem statement by using CAST Query:

DECLARE @itembar varchar(100) = '2567.43'

Select 
    case 
        when cast(@itembar as decimal(18,4))>1.0 
            then 'yes' 
        else 'no' 
    end

Upvotes: 0

Related Questions