Answers.Body: <p>There is another solution here which I have tried and it has updated the data in the db, but currently i can't assess if the outcome was good as the site is borked at the front end for other reasons</p>
  <p><a href="https://stackoverflow.com/questions/47266517/civicrm-crm-core-daovalue-separator-how-to-use-it-in-pure-sql">https://stackoverflow.com/questions/47266517/civicrm-crm-core-daovalue-separator-how-to-use-it-in-pure-sql</a></p>
  <p>so something like</p>
  <pre><code>UPDATE TABLE SET &lt;column&gt;=concat_ws (X'01','value1','value2','etc') WHERE id=x
  </code></pre>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2021-06-18T02:09:23.177Z
Answers.LastActivityDate: 2021-06-18T02:09:23.177Z
Answers.UserDisplayName: petednz - fuzion
Answers.Body: <p>In fact, if you use HEX() function, you can guess it's the ASCII code 01 which is not really readable :</p>
  
  <pre><code>MySQL&gt; SELECT substring(besoins_sp__114, 1,1) 
         FROM civicrm_value_besoins_sp_cifiques_v_nements_26 LIMIT 1;
  +---------------------------------+
  | substring(besoins_sp__114, 1,1) |
  +---------------------------------+
  |                                |
  +---------------------------------+
  1 row in set (0.00 sec)
  
  MySQL&gt; SELECT HEX(substring(besoins_sp__114, 1,1)) 
         FROM civicrm_value_besoins_sp_cifiques_v_nements_26 LIMIT 1;
  +--------------------------------------+
  | HEX(substring(besoins_sp__114, 1,1)) |
  +--------------------------------------+
  | 01                                   |
  +--------------------------------------+
  1 row in set (0.00 sec)
  </code></pre>
  
  <p>From there, you can do reverse operation using UNHEX.
  So, if you need to replace this char by another more readable one, you can do the following :</p>
  
  <pre><code>MySQL&gt; SELECT replace(besoins_sp__114, UNHEX(01), '|') AS special_needs
         FROM civicrm_value_besoins_sp_cifiques_v_nements_26;
  +---------------+
  | special_needs |
  +---------------+
  | |AL|          |
  | |AL|          |
  | |VG|          |
  | |VG|          |
  | |VG|          |
  | |VG|          |
  | |VG|          |
  | |VG|          |
  | |VG|          |
  | |VG|AL|       |
  +---------------+
  </code></pre>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-09-21T11:37:28.037Z
Answers.Text: To add a little part of PHP to the excellent answer by @samuelsov: in PHP you can use the CRM_Core_DAO::VALUE_SEPARATOR to represent the character in question.
Answers.UserDisplayName: ErikH - CiviCooP
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2021-05-28T10:52:03.050Z
Answers.Text: For more info on this CiviCRM delimiter character, see also https://forum.civicrm.org/index.php%3Ftopic=26395.0.html
Answers.UserDisplayName: emma
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-09-20T21:24:33.803Z
Answers.LastActivityDate: 2017-09-20T21:58:24.080Z
Answers.LastEditDate: 2017-09-20T21:58:24.080Z
Answers.UserDisplayName: samuelsov
Body: <p>I need to run a MySQL query to split the values in the fee_level field of the civicrm_participant table. I just can't determine what character to use as the delimiter. When I look at VALUE_SEPARATOR in eclipse, I see a character that resembles a 'binary domino.' When I look at VALUE_SEPARATOR in vi, I see ^A. I'm not sure how to query on either?</p>
  
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2017-09-20T20:47:41.847Z
Comments.Text: Good question. I has caught me out too. Had a dig to find my notes but didn't find them.
Comments.UserDisplayName: petednz - fuzion
Tags: <developer><mysql>
Title: What character is VALUE_SEPARATOR?
ThreadText: # What character is VALUE_SEPARATOR?
  
  (Asked by: username_0 on 2017-09-20)
  
  I need to run a MySQL query to split the values in the fee\_level field of the civicrm\_participant table. I just can't determine what character to use as the delimiter. When I look at VALUE\_SEPARATOR in eclipse, I see a character that resembles a 'binary domino.' When I look at VALUE\_SEPARATOR in vi, I see ^A. I'm not sure how to query on either?
  
  **Tags:** developer, mysql
  
  > **Comments:**
  > **username_1**: Good question. I has caught me out too. Had a dig to find my notes but didn't find them.  
  
  ---
  
  ## 2 answers
  
  ### Answer by username_2 on 2017-09-20. Score: 4 | Accepted answer
  
  In fact, if you use HEX() function, you can guess it's the ASCII code 01 which is not really readable :
  
  ```
  MySQL> SELECT substring(besoins_sp__114, 1,1) 
         FROM civicrm_value_besoins_sp_cifiques_v_nements_26 LIMIT 1;
  +---------------------------------+
  | substring(besoins_sp__114, 1,1) |
  +---------------------------------+
  |                                |
  +---------------------------------+
  1 row in set (0.00 sec)
  
  MySQL> SELECT HEX(substring(besoins_sp__114, 1,1)) 
         FROM civicrm_value_besoins_sp_cifiques_v_nements_26 LIMIT 1;
  +--------------------------------------+
  | HEX(substring(besoins_sp__114, 1,1)) |
  +--------------------------------------+
  | 01                                   |
  +--------------------------------------+
  1 row in set (0.00 sec)
  
  ```
  
  From there, you can do reverse operation using UNHEX.
  So, if you need to replace this char by another more readable one, you can do the following :
  
  ```
  MySQL> SELECT replace(besoins_sp__114, UNHEX(01), '|') AS special_needs
         FROM civicrm_value_besoins_sp_cifiques_v_nements_26;
  +---------------+
  | special_needs |
  +---------------+
  | |AL|          |
  | |AL|          |
  | |VG|          |
  | |VG|          |
  | |VG|          |
  | |VG|          |
  | |VG|          |
  | |VG|          |
  | |VG|          |
  | |VG|AL|       |
  +---------------+
  
  ```
  
  > **Comments:**
  > **username_3**: To add a little part of PHP to the excellent answer by @username_2: in PHP you can use the CRM_Core_DAO::VALUE_SEPARATOR to represent the character in question.  
  > **username_4**: For more info on this CiviCRM delimiter character, see also https://forum.civicrm.org/index.php%3Ftopic=26395.0.html  
  
  ---
  
  ### Answer by username_1 on 2021-06-18. Score: 0
  
  There is another solution here which I have tried and it has updated the data in the db, but currently i can't assess if the outcome was good as the site is borked at the front end for other reasons
  
  <https://stackoverflow.com/questions/47266517/civicrm-crm-core-daovalue-separator-how-to-use-it-in-pure-sql>
  
  so something like
  
  ```
  UPDATE TABLE SET <column>=concat_ws (X'01','value1','value2','etc') WHERE id=x
  
  ```
  
  
  ---
  
  