title: mysql find / replace characters at start and end of a string, but not middle of string
body: <p>Short version:
  I need mysql code that will change </p>
  
  <blockquote>
    <p>[href="http://a-random-domain.com"]hyperlink[/href]</p>
  </blockquote>
  
  <p>into </p>
  
  <blockquote>
    <p>&lt;a href="http://a-random-domain.com"&gt;hyperlink&lt;/a&gt;</p>
  </blockquote>
  
  <ul>
  <li>without messing up the domain part, and  </li>
  <li>without accidentally converting any unrelated instances of "] that might occur in the text of a field. </li>
  <li>Domain will be unique/different every time it is
  encountered.</li>
  </ul>
  
  <p>Long version:
  I am migrating an old database for use with a new application.  The old database has a text field that includes content such as:</p>
  
  <blockquote>
    <p>This is a data field with a [href="http://somedomain.com"]hyperlink[/href] and more data and possibly other hyperlinks.</p>
  </blockquote>
  
  <p>I need to update it to standard html, e.g.</p>
  
  <blockquote>
    <p>This is a data field with a &lt;a href="http://somedomain.com"&gt;hyperlink&lt;/a&gt; and more data and possibly other hyperlinks.</p>
  </blockquote>
  
  <p>Fixing the [href= and [/href] is simple enough using REPLACE</p>
  
  <pre><code>update table set field = replace(field, '[href=', '&lt;a href=');
  </code></pre>
  
  <p>but I get tripped up on the "] closing bracket of the a href tag.  And in the database there are other instances of "] that shouldn't be modified, so I can't just replace on "]</p>
  
  <p>Does mysql have some sort of regex "lookahead" or other way to accomplish this?</p>
  
  <p>Thanks much!</p>