CiviCRM Community Forums (archive)

Hello everyone,

Thought I'd post a solution to a problem we were having. Postal codes have a format of "A9A9A9" but were sometimes entered as "A9A 9A9" or "a9A-9A9" and various combinations of upper case, lower case, hyphens, and no hyphens. This made searches based on postal codes a little difficult. So I did some database cleanup. In case this might be useful to someone else, here's what I did.

To clean up spaces:

   update civicrm_address set postal_code = replace(postal_code," ","");

To clean up hyphens:

   update civicrm_address set postal_code = replace(postal_code,"-","");

To make it all upper case:

   update civicrm_address set postal_code = ucase(postal_code);

Enjoy, and take care out there.

--
Marcel (Writer and Free Thinker at Large) Gagné
Note: This massagee wos nat speel or gramer-checkered.