SQL Server : remove all prefixes, split first and second name - Improve code quality

Question

I have a table of Contacts like this:

Contact:

ID  FirstName                 Lastname     Email                             Title
------------------------------------------------------------------------------------
1   Julia House                            [email protected] 
2   Mr Drew Pettifer                       [email protected] 
3   Nicky Thomas                           [email protected] 
4   Miss Al Cane                           [email protected]
5   Mobile Communication Ltd               [email protected]
6   Fire and Security Ltd 

I have around 150,000 customers that are a mixture of companies and individuals.

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.

Expected output:

ID  FirstName                 Lastname           Email                        Title
-------------------------------------------------------------------------------------
1   Julia                     House              [email protected]
2   Drew                      Pettifer           [email protected]               Mr
3   Nicky                     Thomas             [email protected]
4   Al                        Cane               [email protected]              Miss
5   Mobile                    Communication Ltd  [email protected]
6   Fire                      and Security Ltd

Currently I have:

-- 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

I cannot seem to grab only the first instance after a prefix (read: second world only).

Surely there's also got to be a smarter way of writing this - I'd really like to hear your thoughts.

Thanks

Answer 1

Try this.

DECLARE @str VARCHAR(500)='Mr Sam'

SELECT Title,
       first_name,
       Substring(NAME, CASE
                         WHEN Charindex(' ', NAME) = 0 THEN 1
                         ELSE Charindex(' ', NAME)
                       END, Len(NAME)) last_name
FROM   (SELECT CASE
                 WHEN LEFT(@str, Charindex(' ', @str)) IN( 'Mr', 'Mrs', 'Miss' ) THEN LEFT(@str, Charindex(' ', @str))
                 ELSE ''
               END AS Title,
               CASE
                 WHEN LEFT(@str, Charindex(' ', @str)) IN ( 'Mr', 'Mrs', 'Miss' ) THEN LEFT(Stuff(@str, 1, Charindex(' ', @str), ''), Charindex(' ', Stuff(@str, 1, Charindex(' ', @str), '')))
                 ELSE LEFT(@str, Charindex(' ', @str))
               END AS first_name,
               CASE
                 WHEN LEFT(@str, Charindex(' ', @str)) IN ( 'Mr', 'Mrs', 'Miss' ) THEN Stuff(@str, 1, Charindex(' ', @str), '')
                 ELSE @str
               END NAME) a