CODEWITHSUNDEEP
sqlsql-serversql-server-2008sql-server-2005
Neetu
Neetu

Reputation: 117

Cannot convert varchar to int or subtract them

I have following set of SQL statements. The purpose is to find the Selling price of each item. 

SELECT cost AS 'Price', disc AS 'Discount', (cost-disc) AS 'Selling Price' 
FROM SALE

The problem is that cost, scost, disc columns are of Varchar datatype. So I tried to cast the columns, but it didn't work. No I tried to alter the column datatypes as below: 

UPDATE SALE 
SET cost = '0.0' 
WHERE cost IS NULL OR ISNUMERIC(cost) = 0;

ALTER TABLE SALE 
    ALTER COLUMN cost DECIMAL(12,0);

UPDATE SALE 
SET scost = '0.0' 
WHERE scost IS NULL OR ISNUMERIC(scost) = 0;

ALTER TABLE SALE 
    ALTER COLUMN scost DECIMAL(12,0);

UPDATE SALE 
SET disc = '0.0' 
WHERE disc IS NULL OR ISNUMERIC(disc) = 0;

ALTER TABLE SALE 
    ALTER COLUMN disc DECIMAL(12,0);

The error I am getting in SQL Server Management Studio:

Msg 245, Level 16, State 1, Line 3
Conversion failed when converting the varchar value '0.0' to data type int.

How to solve this problem?

Upvotes: 0

Views: 1680

Answers (3)

Shobhit Gupta
Shobhit Gupta

Reputation: 690

I suggest to create a new table with the required structure and replicate the datain to new table. Then rename your tables. Below is the code:

update SALE set cost='0.0' where cost='NULL' or cost IS NULL OR ISNUMERIC(cost) = 0;
update SALE set scost='0.0' where scost='NULL' or scost IS NULL OR ISNUMERIC(scost) = 0;
update SALE set disc='0.0' where disc='NULL' or disc IS NULL OR ISNUMERIC(disc) = 0;

CREATE TABLE SALENEW (cost decimal(18,2), scost decimal(18,2), disc decimal(18,2));

INSERT INTO SALENEW SELECT * FROM SALE;

EXEC sp_rename 'SALE', 'SALEBACKUP'; 
EXEC sp_rename 'SALENEW', 'SALE';

This will help.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270483

You should fix the data. But I would recommend explicit conversions:

select cost AS Price, disc AS Discount,
       (try_convert(decimal(20, 4), cost) - try_convert(decimal(20, 4), disc) ) as selling_price
from sale;

You should not be storing numbers as strings. You can see if any values are invalid by doing:

select cost, disc
from sale
where try_convert(decimal(20, 4), cost) is null or
      try_convert(decimal(20, 4), disc) is null;

If no rows are returned, then convert them:

alter table alter column cost decimal(20, 4);
alter table alter column disc decimal(20, 4);

Note: This answer uses decimal(20, 4), you can use another relevant type, but decimal seems appropriate for the sample data.

EDIT:

In earlier versions of SQL Server, I would recommend:

select cost AS Price, disc AS Discount,
       ((case when cost not like '%[^0-9.]%' and cost not like '%.%.%'
              then convert(decimal(20, 4), cost)
         end) -
        (case when disc not like '%[^0-9]%'  and cost not like '%.%.%'
              then try_convert(decimal(20, 4), disc)
         end)
       ) as selling_price
from sale;

You can use similar logic. I would not recommend isnumeric() because the following return "1" -- '$', '.', '3e4' -- but none of these convert to a decimal.

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522141

Try converting to a decimal type which has enough precision to cover your actual data set, e.g.

UPDATE SALE SET cost = '0.0' WHERE cost IS NULL OR ISNUMERIC(cost) = 0;
ALTER TABLE SALE ALTER COLUMN cost DECIMAL(12, 4);

The exact error message you are getting seems to be centered around the string literal 0.0 not being convertible to an integer. But, that doesn't sound right assuming you are trying to convert to a decimal, in which case 0.0 is a perfectly acceptable value.

Upvotes: 2

Related Questions