Fixing null sort and display names
Make sure you back up first. 

The display name field for the site concerned only contained first_name, last_name and prefixes. I assume in some case you might also want to append suffixes?

trim and replace are used to remove additional whitespace in cases where their was a null first or last name.

UPDATE civicrm_contact AS c LEFT JOIN civicrm_option_value AS ov ON c.prefix_id = ov.value AND ov.option_group_id = 6 SET c.display_name = trim(replace(concat(ov.label, " ", c.first_name, " ", c.last_name), "  ", " ")) WHERE c.contact_type = 'Individual' and c.display_name is null and (c.first_name is not null or c.last_name is not null);


Check if any contacts have no first or last name - but an email.

SELECT e.email FROM civicrm_contact as c LEFT JOIN civicrm_email as e on c.id = e.contact_id and e.is_primary WHERE c.contact_type = 'Individual' and c.display_name is null and c.first_name is null and c.last_name is null and e.email is not null;


in this case there was none so no need to do a further update.

The same for organizations

UPDATE civicrm_contact as c SET c.display_name = c.organization_name WHERE c.contact_type = 'Organization' and c.display_name is null and organization_name is not null;


Then you want to do the sort name

The select

SELECT c.id, CASE when c.last_name is not null and c.first_name is not null then concat(c.last_name, ", ", c.first_name) when c.last_name is null and c.first_name is not null then c.first_name when c.last_name is not null and c.first_name is null then c.last_name when c.last_name is null and c.first_name is null then e.email END from civicrm_contact as c left join civicrm_email as e on c.id = e.contact_id and e.is_primary where c.contact_type = 'Individual' and c.sort_name is null and (c.first_name is not null or c.last_name is not null or e.email is not null);


and the update

 UPDATE civicrm_contact as c set c.sort_name = CASE when c.last_name is not null and c.first_name is not null then concat(c.last_name, ", ", c.first_name) when c.last_name is null and c.first_name is not null then c.first_name when c.last_name is not null and c.first_name is null then c.last_name END where c.contact_type = 'Individual' and c.sort_name is null and (c.first_name is not null or c.last_name is not null);


Here I used case to resolve issue with multiple cases with both names, only first name only last name.

And finally organizations

SELECT count(c.id) from civicrm_contact as c where c.contact_type = 'Organization' and c.sort_name = null;