将姓氏、名字和后缀提取到单独的列中 [英] Extract last name, first name and suffix into separate columns

问题描述

我想知道是否有人可以提供一种简单的方法将名称提取到不同的列中，如下所示.姓氏后面有一个逗号，名字、中间名首字母和后缀之间有空格.非常感谢.

I was wondering if someone could provide me an easy way to extract the names into different columns as below. There is a comma after the Last Name and space between First Name, Middle Initial, and Suffix. Greatly appreciate it.

存储数据:

Name
Walker,James M JR
Smith,Jack P
Smith,Whitney

要求的结果:

LastName   FirstName   Suffix
Walker     James       JR
Smith      Jack
Smith      Whitney

尝试过的代码:

select top 5 Name,
LEFT(Name, CHARINDEX(',', Name) - 1) AS LastName,
right(Name, len(Name) - CHARINDEX(',', Name)) as FirstName

只是在将名字与中间名首字母和后缀分开时遇到问题.然后从右边的最后一个空格获取 Suffix.

Just having problem with separating First Name from Middle Initial and Suffix. Then getting Suffix from the last space from the right.

推荐答案

您确实应该将名称的这些部分存储在单独的列(第一范式)中以避免此类解析.

You really should store these parts of the name in separate columns (first normal form) to avoid such parsing.

您可以将所有逻辑放入一个巨大的嵌套函数调用中，但使用CROSS APPLY 将它们分离为单个调用非常方便.

You can put all the logic into one huge call of nested functions, but it is quite handy to separate them into single calls using CROSS APPLY.

解析很简单:

• 找到逗号的位置
• 将字符串分成逗号前的部分 (LastName) 和 AfterComma
• 找到第二部分第一个空格的位置AfterComma
• 再次将字符串分成两部分 - 这给出了 FirstName 和其余部分 (AfterSpace)
• 在AfterSpace中找到空格的位置
• 再次将字符串分成两部分 - 这给出了 Initial 和 Suffix.

• find position of comma
• split the string into part before comma (LastName) and part AfterComma
• find position of first space in the second part AfterComma
• split the string into two parts again - this gives FirstName and the rest (AfterSpace)
• find position of space in AfterSpace
• split the string into two parts again - this gives Initial and Suffix.

该查询还会检查 CHARINDEX 的结果 - 如果未找到该字符串，则返回 0.

The query also checks results of CHARINDEX - it returns 0 if the string is not found.

显然，如果字符串值不是预期的格式，您将得到错误的结果.

Obviously, if the string value is not in the expected format, you'll get incorrect result.

DECLARE @T TABLE (Name varchar(8000));
INSERT INTO @T (Name) VALUES
('Walker'),
('Walker,James M JR'),
('Smith,Jack P'),
('Smith,Whitney');

SELECT
    Name
    ,LastName
    ,AfterComma
    ,FirstName
    ,AfterSpace
    ,MidInitial
    ,Suffix
FROM
    @T
    CROSS APPLY (SELECT CHARINDEX(',', Name) AS CommaPosition) AS CA_CP
    CROSS APPLY (SELECT CASE WHEN CommaPosition > 0 THEN 
        LEFT(Name, CommaPosition - 1) ELSE Name END AS LastName) AS CA_LN
    CROSS APPLY (SELECT CASE WHEN CommaPosition > 0 THEN 
        SUBSTRING(Name, CommaPosition + 1, 8000) ELSE '' END AS AfterComma) AS CA_AC

    CROSS APPLY (SELECT CHARINDEX(' ', AfterComma) AS SpacePosition) AS CA_SP
    CROSS APPLY (SELECT CASE WHEN SpacePosition > 0 THEN 
        LEFT(AfterComma, SpacePosition - 1) ELSE AfterComma END AS FirstName) AS CA_FN
    CROSS APPLY (SELECT CASE WHEN SpacePosition > 0 THEN 
        SUBSTRING(AfterComma, SpacePosition + 1, 8000) ELSE '' END AS AfterSpace) AS CA_AS

    CROSS APPLY (SELECT CHARINDEX(' ', AfterSpace) AS Space2Position) AS CA_S2P
    CROSS APPLY (SELECT CASE WHEN Space2Position > 0 THEN 
        LEFT(AfterSpace, Space2Position - 1) ELSE AfterSpace END AS MidInitial) AS CA_MI
    CROSS APPLY (SELECT CASE WHEN Space2Position > 0 THEN 
        SUBSTRING(AfterSpace, Space2Position + 1, 8000) ELSE '' END AS Suffix) AS CA_S

结果

Name                 LastName    AfterComma    FirstName    AfterSpace  MidInitial  Suffix
Walker               Walker
Walker,James M JR    Walker      James M JR    James        M JR        M           JR
Smith,Jack P         Smith       Jack P        Jack         P           P
Smith,Whitney        Smith       Whitney       Whitney

这篇关于将姓氏、名字和后缀提取到单独的列中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！