SQL - How to convert a varchar into int with case

Assuming this is SQL Server, then you can do this:

SELECT productid, 
  CAST((CASE isnumeric(inventory) 
          WHEN 0 THEN 0 
          ELSE CAST(Inventory AS DECIMAL(10, 2)) 
        END) AS INT) AS Inventory 
FROM tablename

SQL Fiddle Demo

This will give you:

| PRODUCTID | INVENTORY |
-------------------------
|         1 |         1 |
|         2 |         0 |
|         3 |        12 |
|         4 |         6 |
|         6 |         2 |

If you want decimal values like 12.5 to come out as ints and not decimals, you'd have to do something like the following to trim the decimal places:

select case when isNumeric(Inventory) = 1 then cast(cast(Inventory as DECIMAL(10,0)) as INT) else 0 end as Inventory_INT, productId
from PRODUCTS

You are much safer to use PatIndex(). IsNumeric is not the best way to check for numeric values in sql-server because it will return 1 for currency symbols as well (ex, isnumerc('$') equals to 1) msdn.

Following example is not rounding up decimal values. If you need to rounded up values then convert inventory to decimal. Sql-Demo Using Patindex() function.

select productId, case patindex('%[0-9]%',inventory) 
                  when 1 then convert(int,convert(decimal(10,2),inventory))
                  else 0 end inventory
from T


| PRODUCTID | INVENTORY |
-------------------------
|         1 |         1 |
|         2 |         0 |
|         3 |        12 |--NOTE
|         4 |         6 |
|         6 |         2 |
|         7 |         0 |--Extra data row added with '$'

To get the rounded up values from inventory;

select productId, case patindex('%[0-9]%',inventory) 
                  when 1 then convert(decimal,inventory)
                  else 0 end inventory
from T

| PRODUCTID | INVENTORY |
-------------------------
|         1 |         1 |
|         2 |         0 |
|         3 |        13 |--NOTE
|         4 |         6 |
|         6 |         2 |
|         7 |         0 |--Extra data row added with '$'

select case when isnumeric(inventory) then
       cast(inventory as INT)
else
       0
end