3

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?

  • 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
3

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|       |
+---------------+
  • 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. – ErikH - CiviCooP Sep 21 '17 at 11:37

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.