如何拆分含无分隔符个人/企业混合名称的字段为姓和名？

我有一个包含100万条记录的数据集，需要将其中的人名拆分为姓和名。目前的核心挑战是Last列同时包含个人姓名和企业名称，而且很多名称没有分隔符。想问问这种拆分是否可行？

示例数据 #

期望结果 #

带分隔符的记录我已经处理完成，现在正在寻找剩余记录的拆分判定标准。我已经通过以下SQL代码处理了简单场景（带分隔符、明显企业名称）：

WITH FirstName_CTE AS
(
    SELECT 
        IndexID, VALUE,
        ROW_NUMBER() OVER (PARTITION BY IndexID ORDER BY IndexID) AS RowNum
    FROM 
        jw_render
    CROSS APPLY
        STRING_SPLIT(Last, ',')
    WHERE 
        (LEN(LAST) < 35) 
        AND (Last LIKE '%,%' AND LAST NOT LIKE '%Medic%') 
        AND (Last LIKE '%,%' AND LAST NOT LIKE '%[0-9]%') 
        AND (Last LIKE '%,%' AND LAST NOT LIKE '%INC%') 
        AND (Last LIKE '%,%' AND LAST NOT LIKE '%LLC%') 
        AND (Last LIKE '%,%' AND LAST NOT LIKE '%Department')
)
-- 使用CTE执行更新操作
UPDATE jw_render
SET LastName = PivotResult.LastName, 
    FirstName = PivotResult.FirstName
FROM jw_render
JOIN
    (SELECT 
         IndexID,
         [1] AS LastName, [2] AS FirstName
     FROM 
         FirstName_CTE
     PIVOT
         (MAX(VALUE)
          FOR ROWNUM IN ([1],[2])) AS PVT
         ) AS PivotResult ON jw_render.IndexID = PivotResult.IndexID;

内容的提问来源于stack exchange，提问作者Jamie W.