title: how do i filter out non-numeric values in a text field in teradata?
body: <p>oI have a teradata table with about 10 million records in it, that stores a numeric id field as a varchar.  i need to transfer the values in this field to a bigint column in another table, but i can't simply say cast(id_field as bigint) because i get an invalid character error.  looking through the values, i find that there could be a character at any position in the string, so let's say the string is varchar(18) i could filter out invalid rows like so :</p>
  
  <pre><code>     where substr(id_field,1,1) not in (/*big,ugly array of non-numeric chars*/)
       and substr(id_field,2,1) not in (/*big,ugly array of non-numeric chars*/)
  
  etc, etc... 
  </code></pre>
  
  <p>then the cast would work, but this is not feasible in the long run. it's slow and if the string has 18 possible characters, it makes the query unreadable.  how can i filter out rows that have a value in this field that will not cast as a bigint without checking each character individually for an array of non-numeric characters?</p>
  
  <p>example values would be </p>
  
  <pre><code>   123abc464
     a2.3v65
     a_356087
     ........
     000000000
     BOB KNIGHT
     1235468099
  </code></pre>
  
  <p>the values follow no specific patterns, I simply need to filter out the ones that contain ANY non-numeric data.
            123456789 is okay but 123.abc_c3865 is not...</p>