Search Replace in MySQL: remove directory structure but keep filename <p>I am changing directory structures in a Drupal installation and need to remove all path data except the file name itself.</p> <p>So the basic structure is:</p> <pre><code>+-------------+--------------+---------+-----------+-------------+----------+-------+----------------------------------------------------------------------------------+-----------------------+ | entity_type | bundle | deleted | entity_id | revision_id | language | delta | field_filename_value | field_filename_format | +-------------+--------------+---------+-----------+-------------+----------+-------+----------------------------------------------------------------------------------+-----------------------+ </code></pre> <p>The filename is stored in <code>field_filename_value</code>. Here's a sample record:</p> <pre><code>+-------------+--------------+---------+-----------+-------------+----------+-------+----------------------------------------------------------------------------------+-----------------------+ | entity_type | bundle | deleted | entity_id | revision_id | language | delta | field_filename_value | field_filename_format | +-------------+--------------+---------+-----------+-------------+----------+-------+----------------------------------------------------------------------------------+-----------------------+ | node | presentation | 0 | 11 | 11 | und | 0 | /really long path name/with lots of words/167 Clarence Ashley - Coo Coo Bird.mp3 | NULL | +-------------+--------------+---------+-----------+-------------+----------+-------+----------------------------------------------------------------------------------+-----------------------+ </code></pre> <p>That ridiculous filename value needs to be changed from:</p> <pre> /really long path name/with lots of words/167 Clarence Ashley - Coo Coo Bird.mp3 </pre> <p>To this:</p> <pre> 167 Clarence Ashley - Coo Coo Bird.mp3 </pre> <p>Setting aside the bad practice of using spaces in file/directory names, how would you correct this? Is it possible using MySQL features alone? </p> <p>As an added challenge, some files may be more than 2 directories deep. </p>