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 =concat_ws (X'01','value1','value2','etc') WHERE id=x
  
  
  ---
  
  