title: Splitting value in MySQL
body: <p>I want to update a field on a really huge (1m rows) table. I want to update it from:</p>
  
  <pre><code>+-----------------------------------------------------------+
  |                            ref                            |
  +-----------------------------------------------------------+
  | 0001___000000000003616655___IVANTI UK___TEMPLATE MATERIAL |
  +-----------------------------------------------------------+
  </code></pre>
  
  <p>to:</p>
  
  <pre><code>+-------------------------------+
  |              ref              |
  +-------------------------------+
  | IVANTI UK___TEMPLATE MATERIAL |
  +-------------------------------+
  </code></pre>
  
  <p>So basically its just changing the <code>ref</code> (which is <strong>not</strong> fixed length) from <code>sid___sku___mfr___pnum</code> to <code>mfr___pnum</code> format.</p>
  
  <p>In PHP I'd do it like so (pseduo code):</p>
  
  <pre><code>list($p['sid'], $p['sku'], $p['mfr'], $p['pnum']) = explode('___', $row['ref']);
  $row['ref'] = $p['mfr'] . '___' . $p['pnum'];
  </code></pre>
  
  <p>Wondering if its possible to do it directly with MySQL with a performant query?</p>