SQL Server isnumeric sees varchar as Hex value, but cast wont change to int
<p>I am dealing with a legacy database that is all kinds of messed up with its datatypes. There are columns that should be <code>integers</code>, but are saved as <code>varchar(9)</code>. I am creating a new version of the table and trying to import the integer values properly. I have the following statement for the fields that need to be converted to int: </p>

<pre><code>CASE 
   ISNUMERIC(fieldname) 
       WHEN 0 THEN NULL 
       ELSE CAST(fieldname AS int) AS fieldname
</code></pre>

<p>The value that keeps tripping this up is: <code>029999E00</code>.</p>

<p>The error is:</p>

<blockquote>
  <p>Conversion failed when converting the varchar value '029999E00' to type int.</p>
</blockquote>

<p>Most of the time, this works great and will give me a valid int value or NULL.</p>

<p>Why is the <code>ISNUMERIC</code> saying this value is int, but then <code>CAST</code> is failing to convert it?  I have tried <code>bigint</code> also and I get the same result.  Is there another way to check for values like this?</p>

<p>There is another post about using ISNUMERIC as the solution, but ISNUMERIC doesn't work in this case.  I need another option to check for a numeric value or to make sure the CAST statement won't fail when it gets the value if it's a Hex value.</p>