4

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?

1
  • Good question. I has caught me out too. Had a dig to find my notes but didn't find them.
    – petednz - fuzion
    Sep 20 '17 at 20:47
4

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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