How to SELECT from between double quotes in a MySQL column and update a column in the same table

Written by James McDonald

September 1, 2016

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;

 

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

The reCAPTCHA verification period has expired. Please reload the page.

You May Also Like…

Clear HSTS Settings in CHrome

Open chrome://net-internals/#hsts enter the domain in the query field and click Query to confirm it has HSTS settings...

Ubuntu on Hyper-v

It boils town to installing linux-azure # as root or sudo apt-get update apt-get install linux-azure...