Change Details

Some 20k records have one 0 as their city. Some are preventing merges Other strings to strip: '0', 'City/Town', 'NoCity', 'City' Note I thought this would find them select * FROM civicrm_address WHERE city = 0 LIMIT 2\G but that returns ones with St. Petersberg as a city!

Some 20k records have one 'City/Town', as their city. Some are preventing merges Other strings to strip: '0', 'City/Town', 'NoCity', 'City' Note I thought this would find them select * FROM civicrm_address WHERE city = 0 LIMIT 2\G but that returns ones with St. Petersberg as a city! Don't do that! select city, count(*) FROM civicrm.civicrm_address WHERE city IN('0', 'city', 'nocity', 'city/town') GROUP BY city LIMIT 20000; +-----------+----------+ | city | count(*) | +-----------+----------+ | 0 | 35 | | City | 119 | | City/Town | 19336 | | NOCITY | 552 | +-----------+----------+

Some 20k records have one 0 'City/Town', as their city. Some are preventing merges Other strings to strip: '0', 'City/Town', 'NoCity', 'City' Note I thought this would find them select * FROM civicrm_address WHERE city = 0 LIMIT 2\G but that returns ones with St. Petersberg as a city! Don't do that! select city, count(*) FROM civicrm.civicrm_address WHERE city IN('0', 'city', 'nocity', 'city/town') GROUP BY city LIMIT 20000; +-----------+----------+ | city | count(*) | +-----------+----------+ | 0 | 35 | | City | 119 | | City/Town | 19336 | | NOCITY | 552 | +-----------+----------+