title: Arithmetic overflow error converting varchar to data type numeric
body: <p>First, let me state I have read various similar posts and haven't been able to identify the similarities between the problem that other posters have had with this error message and the situation I've encountered.  Perhaps I'm not searching correctly, but here's the scenario.  I'm trying to search for values in a table that are less than 70 when converted to a numeric value.  Sometimes the value can be stored with a comma (i.e. 3,080 etc.) so I have a replace statement to remove the comma.  The <code>obsValue</code> column in the queries below is <code>varchar(2000)</code> and I'm guessing that may have something to do with it.  My initial query worked:</p>
  
  <pre><code>Select name, obsValue
  From database.dbo.table
  Where name in ('LDL') 
  and isnumeric(obsvalue) = 1 
  and cast(replace(obsvalue,',','') as decimal(18)) &lt; 70
  </code></pre>
  
  <p>This brings back expected values, but it's not the only <code>name</code> I'm trying to search for.  Other examples include <code>('LDL(CALC)')</code>. Using a <code>UNION</code> statement will allow me to union queries together but unfortunately I don't control the application code and this is not an option.  The only option I have available is using an <code>IN</code> clause, so ultimately the query will look like this when I'm searching for a variety of <code>name</code> values:</p>
  
  <pre><code>Select name, obsValue
  From database.dbo.table
  Where name in ('LDL', 'LDL(CALC)') 
  and isnumeric(obsvalue) = 1 
  and cast(replace(obsvalue,',','') as decimal(18)) &lt; 70
  </code></pre>
  
  <p>And unfortunately doing it this way is where I get the error message.  I apologize if this has already been answered elsewhere.  Please link and I will give credit where credit is due.</p>