Error converting data type varchar to numeric.

Question

i am getting error

Error converting data type varchar to numeric.

Create table #Containerno (CID int,ConWEight varchar(50),Container_Rate decimal(10,4),Trucking_Charges  decimal(10,4),)  
  
  
insert into #Containerno values(444,' ', 22.01,5555)   
  
SELECT  convert(numeric(10,2),(isnull(c.ConWeight,0)*c.Container_Rate)+c.Trucking_Charges) as Invoice_Amount  
FROM     #Containerno c  

Answer 1

Hi @Analyst_SQL
ISNULL: Replaces NULL with the specified replacement value.
However in this sample, 'ConWEight' is blank value instead of NULL. Therefore you could use NULLIF to turn ' ' to NULL first and then use ISNULL, like this:

SELECT  convert(numeric(10,2),(isnull(nullif(c.ConWeight,''),0)*c.Container_Rate)+c.Trucking_Charges) as Invoice_Amount  
FROM  #Containerno c  

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

You try to convert ConWeight = empty string to a numeric; what for a result do you expect? It's not possible to convert a empty string to numeric and that's why you get an error message.
ConWeight sounds like an expect numeric value; why do you strore it as varchar?