Updating contact_sub_type - Not seeing results in search

[mespork]

Hello,

We are in the process of cleaning up some of our data. We have a contact subtype that was not supposed to be used and were to move all contacts to a different subtype. Because there we a more than was allowed by profile update we made the change on the civicrm_contact table. This seems to have updated because we are able to see all contacts through when running a query in mysql, however we are not seeing the correct number reflected when searching based in subcontact type (Inquiry)

The only thing that I noticed is the contacts that do show up in the search have the
Inquiry
(with what looks like boxes with 0001) in the DB dump where one that dont just have Inquiry. Any ideas?

Thanks,

David

[JonGold]

That's the separation character, and that's what's causing your problem.

Take a backup of the database, then insert the separation character before and after your contact subtype data:

Code: [Select]

UPDATE civicrm_contact SET contact_sub_type = CONCAT(UNHEX('01'), contact_sub_type, UNHEX('01') WHERE contact_sub_type IS NOT NULL;

Note that the code above isn't tested on your data, and I don't know what corner cases you might be seeing - but that's the general solution!

cf http://forum.civicrm.org/index.php/topic,24477.msg103328.html#msg103328

[Coleman Watts]

Maybe safer, substitute 'sub_type_name' with the name of your sub-type:

Code: [Select]

UPDATE civicrm_contact SET contact_sub_type = CONCAT(UNHEX('01'), contact_sub_type, UNHEX('01') WHERE contact_sub_type = 'sub_type_name';