title: Select query to remove non-numeric characters
body: <p>I've got dirty data in a column with variable alpha length. I just want to strip out anything that is not 0-9. </p>
  
  <p>I do not want to run a function or proc. I have a script that is similar that just grabs the numeric value after text, it looks like this:</p>
  
  <pre><code>Update TableName
  set ColumntoUpdate=cast(replace(Columnofdirtydata,'Alpha #','') as int)
  where Columnofdirtydata like 'Alpha #%'
  And ColumntoUpdate is Null
  </code></pre>
  
  <p>I thought it would work pretty good until I found that some of the data fields I thought would just be in the format Alpha # 12345789 are not. </p>
  
  <p>Examples of data that needs to be stripped</p>
  
  <pre><code>AB ABCDE # 123
  ABCDE# 123
  AB: ABC# 123
  </code></pre>
  
  <p>I just want the 123. It is true that all data fields do have the # prior to the number. </p>
  
  <p>I tried substring and PatIndex, but I'm not quite getting the syntax correct or something. Anyone have any advice on the best way to address this?</p>