title: MYSQL:Looking for equivalent of Perl's "regex" =~ s/e/i/g => rigix in a MySQL Select
body: <p>Can I match and replace a text pattern in a MYSQL select?</p>
  
  <hr>
  
  <p><strong>EDIT</strong>
  For now it looks like the answer is: <strong>Can't be done,
  since you can't capture what was matched</strong> (from <a href="https://stackoverflow.com/users/95589/eric">Eric</a>'s answer / comments). For now I will look into adding a lookup table.</p>
  
  <hr>
  
  <p>Simplified example:</p>
  
  <p>The MySQL table Coleridge holds many strings like:</p>
  
  <pre><code>text
  ------------------------------------
  In_Xanadu_did_Kubla_Khan
  A_stately_pleasure_dome_decree
  Where_Alph_the_sacred_river_ran
  Through_caverns_measureless_to_man
  Down_to_a_sunless_sea
  </code></pre>
  
  <p>Is there a way to express the select  </p>
  
  <p>SELECT <strong>text =~ s / [ ^_ ] + _ ( .* ) _ [ ^_ ] + $ / \1 /</strong> as replaced FROM Coleridge </p>
  
  <p>and get</p>
  
  <pre><code>replaced
  ________________________
  Xanadu_did_Kubla
  stately_pleasure_dome
  Alph_the_sacred_river
  caverns_measureless_to
  to_a_s
  </code></pre>
  
  <p><strong>Please Note:</strong></p>
  
  <ol>
  <li>The regular expression s/ / / I
  provided is much less complicated
  than what the real world DB contains </li>
  <li>Unfortunately I can't normalize
  the DB ..</li>
  </ol>