Answers.Body: <p>Make sure you back up first. </p>
  
  <p>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?</p>
  
  <p>trim and replace are used to remove additional whitespace in cases where their was a null first or last name.</p>
  
  <pre><code>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);
  </code></pre>
  
  <p>Check if any contacts have no first or last name - but an email.</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>in this case there was none so no need to do a further update.</p>
  
  <p>The same for organizations</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>Then you want to do the sort name</p>
  
  <p>The select</p>
  
  <pre><code>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);
  </code></pre>
  
  <p>and the update</p>
  
  <pre><code> 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);
  </code></pre>
  
  <p>Here I used case to resolve issue with multiple cases with both names, only first name only last name.</p>
  
  <p>And finally organizations</p>
  
  <pre><code>SELECT count(c.id) from civicrm_contact as c where c.contact_type = 'Organization' and c.sort_name = null;
  </code></pre>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2018-02-01T21:04:45.757Z
Answers.LastActivityDate: 2018-02-01T21:04:45.757Z
Answers.UserDisplayName: Luke Stewart
Body: <p>In one of the systems we support there are some contacts with NULL display and sort names. This means they don't show up in searches. How do we fix these?</p>
  
Tags: <mysql><sql><civicrm-contact>
Title: Fixing null sort and display names
ThreadText: # Fixing null sort and display names
  
  (Asked by: username_0 on 2018-02-01)
  
  In one of the systems we support there are some contacts with NULL display and sort names. This means they don't show up in searches. How do we fix these?
  
  **Tags:** mysql, sql, civicrm-contact
  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_0 on 2018-02-01. Score: 2
  
  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;
  
  
  ---
  
  