将姓氏，名字和后缀提取到单独的列中

admin

将姓氏，名字和后缀提取到单独的列中

sql

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

存储的数据：

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

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

阅读 161

2021-06-07

共1个答案

admin

您确实应该将名称的这些部分存储在单独的列中（第一种普通形式），以避免这种解析。

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

解析很简单：

查找逗号的位置
将字符串分成逗号（LastName）和part之前的部分AfterComma
在第二部分中找到第一空间的位置 AfterComma
再次将字符串分成两部分-这给出了FirstName和其余的（AfterSpace）
在中找到空间的位置 AfterSpace
再次将字符串分成两部分-这给出了Initial和Suffix。

该查询还检查结果CHARINDEX-如果找不到该字符串，则返回0。

显然，如果字符串值不是预期的格式，则会得到错误的结果。

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

2021-06-07