title: MySQL selecting words in quotation marks "" within a column of jumbled text
body: <p>I asked a similar question but it has become a larger problem.</p>
  
  <p>The below question catered for 2 options, but not if a single option was stored in the database. See: Use SUBSTRING_INDEX(). Answer by Joyal George:
  <a href="https://stackoverflow.com/questions/26624828/mysql-select-multiple-values-between-in-column/26626458#26626458">MYSQL SELECT multiple values between &quot;&quot; in Column</a> </p>
  
  <p>I have a form in WordPress which captures info on people who want to receive updates in certain areas. They can select 1 or more areas. </p>
  
  <p>I then have a reporting plugin which only accepts SQL to retrieve the data for the report. No application layer, only SQL queries to a MYSQL database</p>
  
  <p>If someone only selects 1 area, I need to extract only that area. If they select more than 1 area I need to extract each area separated by a comma. They can select up to 9 areas.</p>
  
  <p>Data in the column is as follows:</p>
  
  <p>1 area:</p>
  
  <pre><code>Western Cape
  </code></pre>
  
  <p>Multiple areas:</p>
  
  <pre><code>a:3:{i:0;s:10:"North-West";i:1;s:12:"Western Cape";i:2;s:13:"Northern Cape";}
  </code></pre>
  
  <p>I am using a case statement (previous issue with this database structure)</p>
  
  <pre><code>select 
  a.entry_id, 
  MAX(case when field_id = 74 then entry_value end) as FirstName, 
  MAX(case when field_id = 75 then entry_value end) as LastName, 
  MAX(case when field_id = 76 then entry_value end) as Email, 
  MAX(case when field_id = 78 then entry_value end) as Phone, 
  MAX(case when field_id = 79 then
  (select concat( 
      SUBSTRING_INDEX( 
          SUBSTRING_INDEX( 
              SUBSTRING_INDEX( 
                  entry_value,
                  '"',
                  4
              ),
              '"',
              2
          ),
          '"',
          -1
      ),
      ",",
      SUBSTRING_INDEX(
          SUBSTRING_INDEX( 
              SUBSTRING_INDEX(
                  entry_value,
                  '"',
                  4
              ),
              '"',
              4
          ),
          '"',
          -1
      )
  ))
  end) as InterestedIn,
  MAX(case when field_id = 81 then entry_value end) as Province from ch_arf_entry_values a GROUP BY a.entry_id
  </code></pre>
  
  <p>I need to adjust the 'as InterestedIn' to cater for only 1 input value.</p>
  
  <p>I need to find a solution for the last case 'as Province'</p>
  
  <p>Any assistance would be greatly appreciated. </p>