Numeric comparison of a varchar column to a decimal column

Question

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.

which version of sql server are you using? `select @@version as 'sql server version'` — SqlZim, Jan 30 '17 at 18:56
Since you get the error, my guess is that you have something else than numbers there, or for example numbers with wrong decimal separators etc. — James Z, Jan 30 '17 at 18:57

score 1 · Answer 1 · edited May 23 '17 at 11:53

1

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

edited May 23 '17 at 11:53

Community

1
1

answered Jan 30 '17 at 18:58

SqlZim

37,248
6
41
59

isnull(cast(salesdata.saletotal as float(7)),0.0) > isnull(cast(productinfo.productcost as float(7)),0) and am still getting the error. – liquidacid Jan 30 '17 at 19:17
@liquidacid why do you think ISNULLing that would work? – S3S Jan 30 '17 at 19:19
to any nulls in the tables to 0. I am looking on msdn and trying things – liquidacid Jan 30 '17 at 19:28

Hadi · Accepted Answer · 2017-01-30T20:15:54.787

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]%'

edited Jan 30 '17 at 20:15

answered Jan 30 '17 at 19:04

Hadi

36,233
13
65
124

2

I would add a note about how this can reflect "false positives" in your answer, just so the OP understands how that function works. It's definitely a function worth reading up on versus just accepting the name of it as it's expected output.`select isnumeric('$') select isnumeric('12e4') select isnumeric('12,55')` – S3S Jan 30 '17 at 19:07

Numeric comparison of a varchar column to a decimal column

2 Answers2