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


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. – Cynthia Woodard Jan 28 '16 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 Jan 28 '16 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 acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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