title: SQL Server : remove all prefixes, split first and second name - Improve code quality
body: <p>I have a table of <code>Contacts</code> like this:</p>
  
  <p><code>Contact</code>:</p>
  
  <pre><code>ID  FirstName                 Lastname     Email                             Title
  ------------------------------------------------------------------------------------
  1   Julia House                            aaaaaaa@yahoo.co.uk 
  2   Mr Drew Pettifer                       drew@levales.com 
  3   Nicky Thomas                           nicky@nixsphoto.co.uk 
  4   Miss Al Cane                           alcane@talktalk.net
  5   Mobile Communication Ltd               sales@mobilecomms.com
  6   Fire and Security Ltd 
  </code></pre>
  
  <p>I have around 150,000 customers that are a mixture of companies and individuals. </p>
  
  <p>I'd like to split the names and strip strip out all the prefixes and stick them inside title. Any organisations, I don't mind them being a first name only or split between the 2. </p>
  
  <p>Expected output:</p>
  
  <pre><code>ID  FirstName                 Lastname           Email                        Title
  -------------------------------------------------------------------------------------
  1   Julia                     House              aaaaaaa@yahoo.co.uk
  2   Drew                      Pettifer           drew@vales.com               Mr
  3   Nicky                     Thomas             nicky@nixsphoto.co.uk
  4   Al                        Cane               alcane@test.net              Miss
  5   Mobile                    Communication Ltd  sales@mobilecomms.com
  6   Fire                      and Security Ltd
  </code></pre>
  
  <p>Currently I have:  </p>
  
  <pre><code>-- Update Title for all contacts with prefixes
  UPDATE #TempContact 
  SET Title = 
       (SELECT REPLACE(LEFT(Firstname, CHARINDEX(' ', FirstName)), ' ', '') 
        FROM #TempContact TCC
        WHERE #TempContact.ContactID = TCC.ContactID 
          AND (Firstname LIKE 'Mr %' OR 
               Firstname LIKE 'Mrs %' OR  
               Firstname LIKE 'Miss %' OR  
               Firstname LIKE 'Ms %' OR
               Firstname LIKE 'Dr %')
       )
  
  -- Remove those prefixes and split the names
  SELECT 
      SUBSTRING(FirstName, CHARINDEX(' ', FirstName) + 1, LEN(FirstName)),
      SUBSTRING(FirstName, CHARINDEX(' ', FirstName, CHARINDEX(' ', FirstName) + 1), LEN(FirstName))
  FROM 
      #TempContact
  WHERE 
      Firstname LIKE 'Mr %' OR  
      Firstname LIKE 'Mrs %' OR  
      Firstname LIKE 'Miss %' OR  
      Firstname LIKE 'Ms %' OR
      Firstname LIKE 'Dr %'
  
  --After figuring this out, I intended to throw it inside the original update
  </code></pre>
  
  <p>I cannot seem to grab only the first instance after a prefix (read: second world only).</p>
  
  <p>Surely there's also got to be a smarter way of writing this - I'd really like to hear your thoughts. </p>
  
  <p>Thanks</p>