title: MySql Replace and Regex
body: <p>I am trying to remove trailing letters from Building Addresses e.g. <code>111A</code>.</p>
  
  <p>I  can find those patterns with:</p>
  
  <pre><code>Select Address from Table where Address Regexp '^[0-9]{1,6}[A-Z] '
  </code></pre>
  
  <p>However I am unsure how to or if I can include the regexp inside a Replace so that I don't have to do 10*26 different replaces e.g.</p>
  
  <pre><code>Update Table set Address=Replace(Address,'0A','0') where Address like '%0A %'
  </code></pre>
  
  <p>vs some sort of (which I've tried):</p>
  
  <pre><code>Update Table set Address=Replace(Address,regexp ([0-9]{1,6})[A-Z],\1) 
  where Address Regexp '^[0-9]{1,6}[A-Z] '
  </code></pre>