Databases
StackOverflow2013
Postsdbo
POArithmetic overflow error converting varchar to data type numeric
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POArithmetic overflow error converting varchar to data type numeric
    text
    copied!<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>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload