Note the following data:

MariaDB [pallets3]> select options, title from menus where options != '';
+-------------------------------------------------------------------+------------------------------------------------+
| options                                                           | title                                          |
+-------------------------------------------------------------------+------------------------------------------------+
| array('title'=> "Print 150x200 Oil Pallet Labels")                | Print 150x200 Oil Pallet Labels                |
| array('title'=> "Print 150x200 Marg Pallet Labels")               | Print 150x200 Marg Pallet Labels               |
| array('title' => "View/Edit Shipments and Print Shippers")        | View/Edit Shipments and Print Shippers         |
| array('title' => "Track a pallet")                                | Track a pallet                                 |
| array('title' => 'View & change configuration settings')          |                                                |
| array('title' => "View or Edit pack or tub size")                 | View or Edit pack or tub size                  |
| array('title' => "Click to edit standard run rates")              | Click to edit standard run rates               |
| array('title'=> "modify reason codes")                            | modify reason codes                            |
| array('title' => "Click for help and about")                      | Click for help and about                       |
| array('title'=> "Downtime Reports")                               | Downtime Reports                               |
| array('title'=> "View Cost Centres")                              | View Cost Centres                              |
| array('title'=> "Add Cost Centre")                                | Add Cost Centre                                |
| array('title'=> "Production and Downtime Settings")               | Production and Downtime Settings               |
| array('title'=> "Print 100x50 80% 10KG Labels")                   | Print 100x50 80% 10KG Labels                   |
| array('title'=> "Select Reports")                                 | Select Reports                                 |
| array('title'=>"Pentaho Reports Server")                          | Pentaho Reports Server                         |
| array('title'=> "Print 100x50 Labels for Samples")                | Print 100x50 Labels for Samples                |
| array('title'=> "Pallet Labels")                                  | Pallet Labels                                  |
| array('title'=> "Shipping Labels")                                | Shipping Labels                                |
| array('title'=> "Print 100x50 Carton Labels")                     | Print 100x50 Carton Labels                     |
| array('title'=> "Print custom labels - for short or sample runs") | Print custom labels - for short or sample runs |
| array('title'=> "Item Data etc");                                 | Item Data etc                                  |
| array('title'=>"Day of Year Number Calculator")                   | Day of Year Number Calculator                  |
| array('title' => "View Pack Sizes")                               | View Pack Sizes                                |
+-------------------------------------------------------------------+------------------------------------------------+
24 rows in set (0.00 sec)

As you can see I was doing something really dodgy to run a menu system entering a PHP array into a database field and then converting that to a CakePHP options array…

I wanted to make it a bit safer and so I need to select the values between the double quotes and then transfer them across to a new column.

Here is the SQL:

UPDATE menus as t1 
INNER JOIN ( 
    SELECT id, 
    SUBSTRING( 
        options, 
        LOCATE ('\"', options) + 1, 
        LOCATE('\"', options , (LOCATE ('\"', options) + 1)) - LOCATE ('\"', options) -1 ) 
    as opt FROM menus 
) AS t2 
SET t1.title = t2.opt 
WHERE t1.options != '' AND t1.id = t2.id;