title: MySQL string cast to unsigned
body: <p>If I have a string that starts with a number, then contains non-numeric characters, casting this string to an integer in MySQL will cast the first part of the string, and give no indication that it ran into any problems! This is rather annoying.</p>
  
  <p>For example:</p>
  
  <pre><code>SELECT CAST('123' AS UNSIGNED) AS WORKS,
         CAST('123J45' AS UNSIGNED) AS SHOULDNT_WORK,
         CAST('J123' AS UNSIGNED) AS DOESNT_WORK
  </code></pre>
  
  <p>returns:</p>
  
  <pre><code>+-------------+---------------+-------------+
  |    WORKS    | SHOULDNT_WORK | DOESNT_WORK |
  +-------------+---------------+-------------+
  |     123     |      123      |      0      |
  +-------------+---------------+-------------+
  </code></pre>
  
  <p>This doesn't make any sense to me, as clearly, <code>123J45</code> is not a number, and certainly does not equal <code>123</code>. Here's my use case:</p>
  
  <p>I have a field that contains (some malformed) zip codes. There may be mistypes, missing data, etc., and that's okay from my perspective. Because of another table storing Zip Codes as integers, when I join the tables, I need to cast the string Zip Codes to integers (I would have to pad with <code>0</code>s if I was going the other way). However, if for some reason there's an entry that contains <code>6023JZ1</code>, in <em>no way</em> would I want that to be interpreted as Zip Code <code>06023</code>. I am much happier with <code>6023JZ1</code> getting mapped to <code>NULL</code>. Unfortunately, <code>IF(CAST(zipcode AS UNSIGNED) &lt;= 0, NULL, CAST(zipcode AS UNSIGNED))</code> doesn't work because of the problem discussed above.</p>
  
  <p>How do I control for this? </p>