'Numeric comparison of a varchar column to a decimal column

I have the following tables

productinfo:

ID|productname|productarea|productcost|productid

sales:

ID|salesid|productid|

salesdata:

ID|productid|productname|salestotal

Where I am having trouble is: salesdata.salestotal is a varchar column and may have nulls

Comparing the salesdata.saletotal to the productinfo.productcost columns.

I can do a

cast(salesdata.saletotal as float(7)) > X

and it works. I would like to do is

cast(salesdata.saletotal as float(7)) > productinfo.productcost
where     
  sales.productid = salesdata.productid and 
  productinfo.productid = salesdata.productid

However when I do that I get an error:

Error when converting type varchar to float

I found this post which was similar but am unable to get any other columns with it. I can not change the current db structure.

sqlsql-servertsql


Solution 1:[1]

You can add a IsNumeric() to your where clause to check that salesdata.saletotal can be parsed to float

SELECT
cast(salesdata.saletotal as float(7)) > productinfo.productcost
FROM Sales,Salesdata
WHERE     
sales.productid = salesdata.productid and 
productinfo.productid = salesdata.productid and
IsNumeric(salesdata.saletotal) =1

Or you can use Not Like (best than IsNumeric)

salesdata.saletotal NOT LIKE '%[^0-9]%'

Solution 2:[2]

if you are using sql server version 2012 and above, you can use try_cast() or try_parse()

try_cast(salesdata.saletotal as float)>productinfo.productcost

or 

try_parse(salesdata.saletotal as float)>productinfo.productcost

Prior to 2012, I would use patindex('%[^0-9.-]%',salesdata.saletotal)=0 to determine if something is numeric, because isnumeric() is somewhat broken.

e.g. rextester: http://rextester.com/UZE48454

case when patindex('%[^0-9.-]%',salesdata.saletotal)>0 
       then null 
     when isnull(cast(salesdata.saletotal as float(7)),0.0) 
        > isnull(cast(productinfo.productcost as float(7)),0) 
      then 1 
     else 0 
     end

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 Community

Related Questions