How to split CiviCRM multiple database values with SQL?

Question

The CiviCRM database stores multiple values as a varchar field (a string with separator characters). For example, the IDs 3, 9 and 15 are internally saved as #3#9#15# (where # is in fact the character Ctrl+A but displayed as # by phpMyAdmin).

This is strongly recommended against (see https://dba.stackexchange.com/questions/291361/transform-a-varchar-field-with-delimiters-into-multiple-rows ), but I suppose it will not be changed quickly.

For statistical reasons, I wanted to evaluate these varchar fields containing multiple IDs. Therefore, I needed to split them. But as storing data this way is not recommended, SQL has no built-in explode/split function for strings. Also, working with the built-in SQL function substring_index() is no choice because the number of selected options saved in this field varies, from 0 to 15. Additionally, the maximum number is not fix, as new options may be created from time to time.

So I want to share how I figured out to split these CiviCRM values with two SQL calls.

I know that I already posted part of this answer in the Database Administrators Stackexchange but I think this solution is highly CiviCRM specific and will probably be most useful to CiviCRM admins. So I wanted to post it here in a more detailed fashion.

Answer 1

For every option group (that is, set of multiple values) I created two SQL views: One for splitting the varchar field correctly, and one for combining the split values with other data. All of these "splitting views" in the database pull data from an additional, common view named match_ufid_contactid (matching the Drupal user ID uf_id with a CiviCRM contact) and another view named case_years (containing the information in which year the first consultation of this case took place, which is a custom field in this system).

So, let's say we have a custom field/column case_result of type multiple-choice (residing in the normal CiviCRM database called civi_db here, in a table called result_table here). We are splitting this column into single values with the following SQL view named stringsplit_case_result (residing in a second statistics database called stats_db here):

select `zz`.`case_id` AS `case_id`,
       `zz`.`contact_id` AS `contact_id`,
       `zz`.`one_split` AS `one_split`
from
     (select cast(coalesce(nullif(substring_index(substring_index(`content_alias`.`fieldset_column`,char(1),`num`.`id`),char(1),-1),''),'0')
         as signed) AS `one_split`,
         `content_alias`.`case_id` AS `case_id`,
         `content_alias`.`contact_id` AS `contact_id`
     from
          ((select `fieldset_table`.`case_result` AS `fieldset_column`,
                   `case_contact`.`case_id` AS `case_id`,
                   `case_contact`.`contact_id` AS `contact_id`
          from (`civi_db`.`result_table` `fieldset_table`
          join `civi_db`.`civicrm_case_contact` `case_contact`
              on(`case_contact`.`case_id` = `fieldset_table`.`entity_id`))) `content_alias`
          join
              (select `a`.`h` * 10 + `b`.`t` + 1 AS `id`
              from ((select 0 AS `h` union select 1 AS `1` union select 2 AS `2` union select 3 AS `3` union select 4 AS `4` union select 5 AS `5` union select 6 AS `6` union select 7 AS `7` union select 8 AS `8` union select 9 AS `9`) `a`
              join (select 0 AS `t` union select 1 AS `1` union select 2 AS `2` union select 3 AS `3` union select 4 AS `4` union select 5 AS `5` union select 6 AS `6` union select 7 AS `7` union select 8 AS `8` union select 9 AS `9`) `b`)) `num`)
     where `num`.`id` > 0) `zz`
where `zz`.`one_split` > 0
order by `zz`.`one_split`

Then, inside stats_db, we create another SQL view that takes the split values from the stringsplit_case_result view and matches them with useful information we need for statistics (namely, the CaseID, the ContactID, the Drupal user ID, the year of first consultation, the human-readable label of this split value):

select `stringsplit`.`case_id` AS `case_id`,
       `stringsplit`.`contact_id` AS `contact_id`,
       `stringsplit`.`one_split` AS `one_split`,
       `option_table`.`label` AS `label`,
       `uf_table`.`uf_id` AS `uf_id`,
       years_table.first_contact AS first_contact
from `stats_db`.`stringsplit_case_result` `stringsplit`
    join (select `optionvalue`.`label` AS `label`,
                 `optionvalue`.`value` AS `value`
         from `civi_db`.`civicrm_option_value` `optionvalue`
        where `optionvalue`.`option_group_id` = 136) `option_table`
    join `stats_db`.`match_ufid_contactid` `uf_table`
    join `civi_db`.civicrm_case `case_table`
join `stats_db`.case_years `years_table`
where case_table.is_deleted = 0
  and `stringsplit`.`one_split` = `option_table`.`value`
  and `uf_table`.`contact_id` = `stringsplit`.`contact_id`
  and `stringsplit`.`case_id` = case_table.id
and stringsplit.case_id = years_table.case_id

These views rely on two additional SQL views called case_years and match_ufid_contactid (both inside stats_db). case_years supposes there is a table called first_consultation with a column date in the civi_db database, where date is in SQL date format. You can find them here:

match_ufid_contactid:

select `civi_db`.`civicrm_uf_match`.`uf_id` AS `uf_id`,
`civi_db`.`civicrm_uf_match`.`contact_id` AS `contact_id` 
from `civi_db`.`civicrm_uf_match`

and case_years:

select `case_table`.`id` AS `case_id`,
extract(year from `first_consultation`.`date`) AS `first_contact` 
from 
(`civi_db`.`civicrm_case` `case_table` 
join `civi_db`.`first_consultation` `first_consultation`) 
where `case_table`.`id` = `first_consultation`.`entity_id`

Edit: @petednz In our system, Drupal Views doesn't recognize the CiviCRM fields as multiple value fields, which is why it doesn't allow to split the fields in Drupal.

Answer 2

While not a SQL-specific answer, I would typically use an ETL tool like Pentaho Data Integration to do the data transforms SQL isn't well-suited for. PDI has both a "Split fields" and "Split fields into rows" steps - depending on whether you want your data normalized or denormalized.

To deal with the control character issue, I'll generally use UNHEX(01) though it seems like your CHAR(1) serves the same purpose.