将姓氏、名字和后缀提取到单独的列中 [英] 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 partAfterComma
- 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
andSuffix
.
该查询还会检查 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屋!