Answers.Body: <p>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):</p>
  
  <pre><code>$typeIds = explode(CRM_Core_DAO::VALUE_SEPARATOR, $value);
  $value   = array();
  foreach ($typeIds as $typeId) {
    $value[$typeId] = $this-&gt;_caseTypes[$typeId];
  }
  $rows[$rowNum]['case_type'] = implode(', ', $value);
  </code></pre>
  
  <p>Or if you need to create a query and use the VALUE_SEPARATOR in a clause do something like this:</p>
  
  <pre><code> $where .= " ( " . $field . " LIKE CONCAT ('%" . CRM_Core_DAO::VALUE_SEPARATOR . "'," . $filter['value'] . ",'" . CRM_Core_DAO::VALUE_SEPARATOR . "%') ) AND ";
  </code></pre>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2016-01-28T15:42:32.543Z
Answers.LastActivityDate: 2016-01-28T15:42:32.543Z
Answers.UserDisplayName: ErikH - CiviCooP
Answers.Body: <p>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.</p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2016-01-28T16:21:15.103Z
Answers.Text: 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.
Answers.UserDisplayName: Cynthia Woodard
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2016-01-28T18:59:25.650Z
Answers.Text: 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. https://raw.githubusercontent.com/civicrm/civicrm-drupal/7.x-4.6/modules/views/civicrm/civicrm_handler_field_custom.inc
Answers.UserDisplayName: Demerit
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2016-01-28T08:20:20.530Z
Answers.LastActivityDate: 2016-01-28T08:20:20.530Z
Answers.UserDisplayName: Demerit
Body: <p>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. </p>
  
  <p>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:</p>
  
  <p><a href="https://i.sstatic.net/bg0fQ.png" rel="nofollow noreferrer"><img src="https://i.sstatic.net/bg0fQ.png" alt="Extra characters"></a></p>
  
  <p>Anyone know why they might have ended up in the database? And how to avoid them showing up again?</p>
  
  <p>Thanks!</p>
  
  <p>I think this was also the problem behind <a href="https://civicrm.stackexchange.com/questions/9009/how-can-i-make-a-filter-to-exclude-someone-who-has-a-certain-tag-assigned-to-the">another question I asked here</a> previously.</p>
  
  <p>drupal 7
  civicrm 4.6.11</p>
  
Tags: <drupal><mysql>
Title: non-asci characters ending up in database
ThreadText: # non-asci characters ending up in database
  
  (Asked by: username_0 on 2016-01-28)
  
  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](https://i.sstatic.net/bg0fQ.png)](https://i.sstatic.net/bg0fQ.png)
  
  Anyone know why they might have ended up in the database? And how to avoid them showing up again?
  
  Thanks!
  
  I think this was also the problem behind [another question I asked here](https://civicrm.stackexchange.com/questions/9009/how-can-i-make-a-filter-to-exclude-someone-who-has-a-certain-tag-assigned-to-the) previously.
  
  drupal 7
  civicrm 4.6.11
  
  **Tags:** drupal, mysql
  
  
  ---
  
  ## 2 answers
  
  ### Answer by username_2 on 2016-01-28. Score: 4 | Accepted answer
  
  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.
  
  > **Comments:**
  > **username_0**: 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.  
  > **username_2**: 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. https://raw.githubusercontent.com/civicrm/civicrm-drupal/7.x-4.6/modules/views/civicrm/civicrm_handler_field_custom.inc  
  
  ---
  
  ### Answer by username_1 on 2016-01-28. Score: 1
  
  To enhance a little on username_2'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 ";
  
  
  ---
  
  