I would like to merge some custom fields in my database. I have a set of custom fields and in this set i have a custom field with multiple options, I will like now to merge some option fields for this custom field.

example: option field A = tomato|tomato , option field B = apple|apple , option field C = fruit|fruit ,

I want to merge A and B to C, note that both A, B and C have data.

Is this possible and how can i accomplish this without loosing any data? Civi version 4.5.5 and Drupal


First create a Temp table with same structure and move all the data to the temp table. And then create a new column X and merge both the column A and B together and insert the values into the new column X

And once you are happy with the merge column X move the same against custom table matching with the entity ID - Job done

Please refer to the below link for Example http://www.tutorialspoint.com/mysql/mysql-concat-function.htm

  • Are you looking for the SQL - And I guess you are trying to do this in the Database Level - right ? Mar 2 '16 at 11:35
  • Yes i will do this at the database level, have not started yet and will update you here.
    – Kboy
    Mar 2 '16 at 11:47
  • Hi Ramesh, i decided to do it manually, for all activities with option A or B, i changed it manually to option C and after the process i disabled options A and B.
    – Kboy
    Mar 10 '16 at 9:59

Ramesh's answer is good, but you have to be very careful to be aware of the VALUE_SEPARATOR character - which by default is Unicode 0x0001. In multi-option fields, there's a separation character at the beginning, end, and in between each option recorded in the database. Most text editors won't render it, so it's easy to miss.

This thread on the old CiviCRM forums discusses this issue: https://forum.civicrm.org/index.php?topic=24477.0

I have a response on that thread that gives good advice for how to work with that character in MySQL: https://forum.civicrm.org/index.php?topic=24477.msg103328#msg103328


A non-db solution for others who might read this would be to do an export of the contact-id (assuming these are contact fields), then in a spreadsheet use a CONCAT or CONCATENATE formula to get all the data in to a single cell with commas inbetween - don't worry if you end up with 2 commas adjacent etc, so the resulting cell might look like




Then, if you have added the new options to your field, you should be able to import this to 'update' the existing data in the field that you want the combined data to be stored in

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.