SQL Server : remove all prefixes, 拆分名和姓 - 提高代码质量

Question

我有一个 Contacts 表，如下所示:

联系方式:

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 

我有大约 150,000 名既有公司也有个人的客户。

我想拆分名称并剥离所有前缀并将它们粘贴在标题中。任何组织，我不介意它们只是名字或两者分开。

预期输出:

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

目前我有:

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

我似乎无法只抓取前缀后的第一个实例(阅读:仅限第二世界)。

当然还有更聪明的写法 - 我真的很想听听你的想法。

谢谢

Answer 1

试试这个。

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