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…

Squarespace Image Export

To gain continued access to your Squarespace website images after cancelling your subscription you have several...

MySQL 8.x GRANT ALL STATEMENT

-- CREATE CREATE USER 'tgnrestoreuser'@'localhost' IDENTIFIED BY 'AppleSauceLoveBird2024'; GRANT ALL PRIVILEGES ON...

Exetel Opt-Out of CGNAT

If your port forwards and inbound and/or outbound site-to-site VPN's have failed when switching to Exetel due to their...