What character is VALUE_SEPARATOR?

Question

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?

Answer 1

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|       |
+---------------+

Answer 2

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