Today we spent about 2 hours bashing our heads trying to get a string compare to work so we could run a query against contact sub type while setting up a new participant public listing.

We started realizing what was going on when the string length getting returned was 7. When it should have been 5, for Child. Took a look at it in the command line and saw this:

Extra characters

Anyone know why they might have ended up in the database? And how to avoid them showing up again?


I think this was also the problem behind another question I asked here previously.

drupal 7 civicrm 4.6.11

2 Answers 2


CiviCRM historically uses the CRM_Core_DAO::VALUE_SEPARATOR character as a serialization technique for storing multiple values in a field. Best practice is use the API for accessing values, or if you really want to do direct database access then you need to treat that value as a multiple value separator.

  • Oops. That makes sense. So would Drupal Views do this correctly then and use the API? Because the behavior I was seeing there, unable to query custom data field values unless I set to the fields to a text field and then searched for NULL fields in order to exclude records in the results strikes me as very similar to what we were seeing with this. Commented Jan 28, 2016 at 16:21
  • I'm not sure about views. Just browsing the code I would assume it's in the render() function here and it seems to handle it appropriately, but I can't confirm just by looking this is actually where it happens. raw.githubusercontent.com/civicrm/civicrm-drupal/7.x-4.6/…
    – Demerit
    Commented Jan 28, 2016 at 18:59

To enhance a little on Demerit's correct answer, this is what I do in PHP when I need to separate contact_sub_types or case_types (which have the same setup in the database):

$typeIds = explode(CRM_Core_DAO::VALUE_SEPARATOR, $value);
$value   = array();
foreach ($typeIds as $typeId) {
  $value[$typeId] = $this->_caseTypes[$typeId];
$rows[$rowNum]['case_type'] = implode(', ', $value);

Or if you need to create a query and use the VALUE_SEPARATOR in a clause do something like this:

 $where .= " ( " . $field . " LIKE CONCAT ('%" . CRM_Core_DAO::VALUE_SEPARATOR . "'," . $filter['value'] . ",'" . CRM_Core_DAO::VALUE_SEPARATOR . "%') ) AND ";

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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