根据字符位置分隔字段

Question

我有一个名为“名”的字段，我将其划分为第一、最后和中间的首字母。我的查询将它分为第一名和最后一名，但是名字包含中间的首字母。有些名称有中间的首字母，有些有它和句点，有些没有，有些有完整的单词&其他名称是不适用的组的名称。这是我的问题。

SELECT Name,
   CASE WHEN Name LIKE '%,%' THEN REPLACE(SUBSTRING(Name, 1, (CHARINDEX(',', Name))), ',', '') ELSE Name END AS LastName,
   CASE WHEN Name LIKE '%,%' THEN SUBSTRING(Name, (CHARINDEX(',', Name) + 2), LEN(Name)) ELSE Name END AS FirstName
FROM Customer

我怎么能：

1. 将name列上的子字符串更改为仅到该字段中的空格。
2. 只将中间名称的第一个字母放入MI字段

当前结果

​

​

期望结果

​

​

Answer 1

好吧，对不起，这是一份真正的黑客工作。

我常用的处理方法是在临时表中进行处理--使用一种方法更新符合特定条件的行；如果第一种方法没有捕捉到行，则使用另一种方法；并重复执行，直到没有剩余的行(或仅手动修改的行)。

在这种情况下，我以与您在问题中类似的方式完成了第一步(虽然我使用左边和料子来查找名称组件--我发现它们的问题比LEN少，有时也比SUBSTRING少)。

但是，我在CTE中使用这些结果作为处理的下一个步骤的输入:如果第一个名称有一个空格，则将该名称的左组件作为第一个名称，然后左(1)剩下的部分获得初始值。

在这种情况下，CTE的操作类似于我对临时表所做的操作。

这是一个带有数据的db<>fiddle。

请注意，这里有一些额外空间的处理(使用LTRIM和RTRIM)，但这一点远远不够完美。在下面的数据中，我还为Cat, Sylvester The添加了额外的行，并举例说明了间距的变化。

CREATE TABLE #Names (FullName nvarchar(100));
INSERT INTO #Names (FullName) VALUES
('Duck, Daffy'),
('Bunny, Bugs F.'),
('Cartoon Network'),
('Doo, Scooby D'),
('Cat, Sylvester The'),
('Cat,Sylvester The'),
('Cat,Sylvester     The');

WITH NameSplit AS
        (SELECT     FullName,
                    LTRIM(RTRIM(CASE WHEN FullName LIKE '%,%' THEN LEFT(FullName, CHARINDEX(',',FullName)-1) ELSE FullName END)) AS LastName,
                    LTRIM(RTRIM(CASE WHEN FullName LIKE '%,%' THEN STUFF(FullName, 1, CHARINDEX(',',FullName), '') ELSE FullName END)) AS NameRest
            FROM    #Names
        )
    SELECT      FullName,
                LastName,
                CASE WHEN FullName LIKE '%,%' AND NameRest LIKE '% %' THEN LEFT(NameRest, CHARINDEX(' ', NameRest)-1) ELSE NameRest END AS FirstName,
                CASE WHEN FullName LIKE '%,%' AND NameRest LIKE '% %' THEN LEFT(LTRIM(STUFF(NameRest, 1, CHARINDEX(' ', NameRest), '')), 1) ELSE '' END AS MiddleInitial
    FROM        NameSplit

结果

FullName               LastName         FirstName        MiddleInitial
Duck, Daffy            Duck             Daffy    
Bunny, Bugs F.         Bunny            Bugs             F
Cartoon Network        Cartoon Network  Cartoon Network 
Doo, Scooby D          Doo              Scooby           D
Cat, Sylvester The     Cat              Sylvester        T
Cat,Sylvester The      Cat              Sylvester        T
Cat,Sylvester     The  Cat              Sylvester        T

Answer 2

对于这些类型的清理，我的方法是使用union all，并使用where子句对单个select的解析逻辑进行划分。这个方法和你接受的答案并没有太大的不同，但我会把它留在那里，以防你发现它有帮助。

with cte as

(select *, 
       trim(left(fullname,charindex(',',fullname+',')-1)) as fname,
       trim(stuff(fullname,1,charindex(',',fullname),'')) as lname
 from your_table)

select *, left(trim(stuff(lname,1,charindex(' ',lname),'')),1) as mid_init  
from cte
where fullname like'%,%' and lname like '% %' --when it looks like we have a middle name
union all
select *,null
from cte  
where fullname not like'%,%' or lname not like '% %' -- no middle name