将全名拆分为首、中、尾和后缀SQL

Question

我试图解析一个全名读作名，中间名，姓和后缀。我有一半的SQL代码，我已经尝试过，但我需要帮助获得基于以下逻辑的后缀。

如果没有Suffix，则将字段保留为NULL如果没有"中间名"，则将字段保留为NULL

，Ltrim(SubString(NAME, 1, Isnull(Nullif(CHARINDEX(' '， NAME)， 0)， 1000))) AS FirstName，Ltrim(SUBSTRING(NAME, CharIndex(' '， NAME)， CASE当(CHARINDEX(' '， NAME, CHARINDEX(' '， NAME) + 1) - CHARINDEX(' '， NAME)) <= 00charindex (' '， name, charindex (' '， name) + 1) - charindex (' '， name)END))

,Ltrim(SUBSTRING(NAME, 
Isnull(Nullif(CHARINDEX(' ', NAME, Charindex(' ', NAME) + 1), 0), CHARINDEX(' ', NAME)), CASE 
WHEN Charindex(' ', NAME) = 0
THEN 0
ELSE LEN(NAME)
END)) AS LastName   

Thanks in advance

Example: 
an example of the full name is Alexander BC Brendel Jr , The desired outcome would be First - Alexander Middle - BC Last - Brendel Suffix - Jr 

|HdrName                |FirstName |MiddleName  |LastName   |Suffix|
|--------               |--------- |----------- |---------- |------|
|Alexander BC Brendel Jr|Alexander |BC          |Brendel    |Jr    |
|Thomas J Abraham  Sr   |Thomas    |J           |Abraham    |Sr    |

Answer 1

如果HdrName的格式柱是恒定的，由四个部分组成，那么你很幸运-在SQL Server中有一个特殊的功能完全适合你的情况:

DECLARE @Names TABLE (HdrName VARCHAR(100));
INSERT INTO @Names VALUES ('Alexander BC Brendel Jr'), ('Thomas J Abraham Sr');
WITH PreparedNames AS 
(
SELECT 
HdrName,
FullName = REPLACE(HdrName, ' ', '.')
FROM
@Names
)
SELECT
HdrName     ,
Suffix      =   PARSENAME(FullName, 1),
FirstName   =   PARSENAME(FullName, 4),
MiddleName  =   PARSENAME(FullName, 3),
LastName    =   PARSENAME(FullName, 2)
FROM
PreparedNames;

PARSENAME函数用于解析由四部分组成的SQL Server对象的名称，但是如果将名称中的空格替换为点，则可以将名称解析为SQL Server对象。

Answer 2

我使用了函数[dbo]。[DelimitedSplit8K] from here:

https://www.sqlservercentral.com/articles/tally-oh-an-improved-sql-8k-%e2%80%9ccsv-splitter%e2%80%9d-function

然后我创建了自己的函数来确定应该标记和返回名称的哪些部分。这可能是一种糟糕的方法，但我管理的系统严重依赖于从大型机正确解析的名称，这是我能想到的最好的方法。

/**************************************************************************************************
Name:        GetMultiNamePart
Author:      Tommy Harris
Date:        February 13, 2023
Dependences: None            
Description: Name parser that breaks out the parts of a name into Title, First, Middle, Last and Suffix.
Parameters:
@FullName - Name to be split
@Part - First, Middle, Last, Suffix, Title
@Order - Order of the name to parse. (LFMS,LSFM,FMLS)
1. LFMS: Last, First, Middle, Suffix
2. LSMS: Last, Suffix, First, Middle
3. FMLS: First, Middle, Last, Suffix
*****************************************************************************************************/
CREATE FUNCTION [dbo].[GetMultiNamePart]
(
@FullName   varchar(8000) = '',
@Part       varchar(15) = '',
@Order      varchar(5) = 'LFMS'
)
RETURNS VARCHAR(120)
AS
BEGIN
DECLARE @NameToReturn   VARCHAR(120) = ''
DECLARE @NamePartCount  INT
/******************************************************************
/Declare the Name Parts table where the individual parts will be stored.
******************************************************************/
DECLARE @NameParts TABLE
(
ItemNumber  INT,
Item        VARCHAR(8000),
ItemType    VARCHAR(50)
)
/******************************************************************
/Insert the split out name parts
******************************************************************/
INSERT INTO @NameParts
(
ItemNumber,     Item,       ItemType
)
SELECT 
ItemNumber,     Item,       ''
FROM dbo.DelimitedSplit8K(replace(replace(@FullName, ' ', ','), '  ',' '), ',') p
/******************************************************************
/ Find the entries relating to Titles, update the Item Type.
******************************************************************/
UPDATE @NameParts
SET ItemType = 'Title'
WHERE Item IN ('Dr','Dr.','Mr','Mr.','Mrs','Mrs.','Hon','Hon.','Miss','Miss.','Sir','Sir.')
/******************************************************************
/ Find the entries relating to Suffix, update the Item Type.
******************************************************************/
UPDATE @NameParts
SET ItemType = 'Suffix'
WHERE Item IN ('Jr.','Jr','Sr.','Sr','Esq','Esq.','I','II','III','IV','I.','II.','III.','IV.','V','VI','V.','VI.')
/******************************************************************
/ Delete any entries that are blank. This could be erroneous entries
/ because there are 2 spaces between name parts.
******************************************************************/
DELETE FROM @NameParts
WHERE RTRIM(Item) = ''
/******************************************************************
/ Get the count of the number of parts of the name, minus the suffix.
******************************************************************/
SET @NamePartCount = (SELECT COUNT(*) FROM @NameParts WHERE ItemType = '')
/******************************************************************
/ Check the number of parts of the name to determine how to parse them.
/ TO DO: check formats again with more test data.
******************************************************************/
IF @NamePartCount > 2
BEGIN
/******************************************************************
/ Check the format of the name to determine the order that the first
/ and last names get assigned. Assign the remaining name parts in the 
/ middle as middle names.
******************************************************************/
IF @Order IN ('LSFM','LFMS')
BEGIN
With ln As
(
SELECT TOP 1 ItemNumber,ItemType
FROM @NameParts
WHERE ItemType = ''
ORDER BY ItemNumber ASC
)
UPDATE ln SET ItemType = 'Last'
WHERE ItemNumber = ln.ItemNumber; --DIBE
With fn As
(
SELECT TOP 1 ItemNumber,ItemType
FROM @NameParts
WHERE ItemType = ''
ORDER BY ItemNumber ASC
)
UPDATE fn SET ItemType = 'First'
WHERE ItemNumber = fn.ItemNumber;
UPDATE @NameParts 
SET ItemType = 'Middle'
WHERE ItemType = ''
END
ELSE IF @Order = 'FMLS'
BEGIN
With fn As
(
SELECT TOP 1 ItemNumber,ItemType
FROM @NameParts
WHERE ItemType = ''
ORDER BY ItemNumber ASC
)
UPDATE fn SET ItemType = 'First'
WHERE ItemNumber = fn.ItemNumber;
With ln As
(
SELECT TOP 1 ItemNumber,ItemType
FROM @NameParts
WHERE ItemType = ''
ORDER BY ItemNumber DESC
)
UPDATE ln SET ItemType = 'Last'
WHERE ItemNumber = ln.ItemNumber;
UPDATE @NameParts 
SET ItemType = 'Middle'
WHERE ItemType = ''
END
END
ELSE IF @NamePartCount = 2
BEGIN
/******************************************************************
/ Check the format of the name to determine the order that the first
/ and last names get assigned. Middle names do not exist in this.
******************************************************************/
IF @Order IN ('LSFM','LFMS')
BEGIN
With ln As
(
SELECT TOP 1 ItemNumber,ItemType
FROM @NameParts
WHERE ItemType = ''
ORDER BY ItemNumber ASC
)
UPDATE ln SET ItemType = 'Last'
WHERE ItemNumber = ln.ItemNumber;
With fn As
(
SELECT TOP 1 ItemNumber,ItemType
FROM @NameParts
WHERE ItemType = ''
ORDER BY ItemNumber ASC
)
UPDATE fn SET ItemType = 'First'
WHERE ItemNumber = fn.ItemNumber;
END
ELSE IF @Order = 'FMLS'
BEGIN
With fn As
(
SELECT TOP 1 ItemNumber,ItemType
FROM @NameParts
WHERE ItemType = ''
ORDER BY ItemNumber ASC
)
UPDATE fn SET ItemType = 'First'
WHERE ItemNumber = fn.ItemNumber;
With ln As
(
SELECT TOP 1 ItemNumber,ItemType
FROM @NameParts
WHERE ItemType = ''
ORDER BY ItemNumber DESC
)
UPDATE ln SET ItemType = 'Last'
WHERE ItemNumber = ln.ItemNumber;
END
END
ELSE IF @NamePartCount = 1
BEGIN
/******************************************************************
/ If only one unassigned ItemType exists, assume its the first name.
******************************************************************/
With fn As
(
SELECT TOP 1 ItemNumber,ItemType
FROM @NameParts
WHERE ItemType = ''
ORDER BY ItemNumber DESC
)
UPDATE fn SET ItemType = 'First'
WHERE ItemNumber = fn.ItemNumber;       
END
/******************************************************************
/ Return the part of the name as the output value based on the part requested
******************************************************************/
IF @Part = 'Middle' 
BEGIN
/******************************************************************
/ Concatenate the middle name fields.
******************************************************************/
SET @NameToReturn = ISNULL((SELECT STUFF( (SELECT ' ' + Item 
FROM @NameParts 
WHERE ItemType = 'Middle'
ORDER BY ItemNumber
FOR XML PATH('')), 
1, 1, '')),'')
END
ELSE IF @Part = 'Full' 
BEGIN
SET @NameToReturn = ISNULL((SELECT TOP 1 Item FROM @NameParts WHERE ItemType = 'First') + ' ','')
SET @NameToReturn = @NameToReturn + ISNULL((SELECT STUFF( (SELECT ' ' + Item 
FROM @NameParts 
WHERE ItemType = 'Middle'
ORDER BY ItemNumber
FOR XML PATH('')), 
1, 1, '')) + ' ','')
SET @NameToReturn = @NameToReturn + ISNULL((SELECT TOP 1 Item FROM @NameParts WHERE ItemType = 'Last') + ' ','')
SET @NameToReturn = @NameToReturn + ISNULL((SELECT TOP 1 Item FROM @NameParts WHERE ItemType = 'Suffix'),'')
SET @NameToReturn = RTRIM(@NameToReturn)
END
ELSE
BEGIN
/******************************************************************
/ Get just the part of the name requested.
******************************************************************/
SET @NameToReturn = ISNULL((SELECT TOP 1 Item FROM @NameParts WHERE ItemType = @Part),'')
END
RETURN @NameToReturn
END