algorithm - 从 FirstName、LastName 和 MiddleName 生成单词排列

Question

假设我有以下名字:John Smith Alexander。我想得到 John Alexander Smith； 史密斯·约翰·亚历山大； 亚历山大·史密斯·约翰; 等

中间名不必存在，所以来自 John Smith 我应该只收到 John Smith 和 Smith John.

全名也可能包含 4 个或更多单词，但这种情况很少见。

Answer 1

先把字符串打散:

DECLARE @separator char(1)
DECLARE @NameParts TABLE (PartId INT IDENTITY, part varchar(50))
DECLARE @Name varchar(50)
SET @separator = ' '
SET @name = 'John Smith Alexander'
;WITH Parts(pn, start, finish) AS (
  SELECT 1, 1, CHARINDEX(@separator, @Name)
  UNION ALL
  SELECT pn + 1, finish + 1, CHARINDEX(@separator, @Name, finish + 1)
  FROM Parts
  WHERE finish > 0
)
INSERT INTO @NameParts(part)
SELECT SUBSTRING(@Name, start, CASE WHEN finish > 0 THEN finish-start ELSE 50 END)
FROM Parts

然后得到排列:

DECLARE @tokencount int
SELECT @tokencount = COUNT(*) FROM @NameParts

;WITH Subsets AS 
(
SELECT CAST(' ' + part AS VARCHAR(MAX)) Permutation,
CAST(1 AS INT) AS Iteration
FROM @NameParts 
UNION ALL
SELECT Permutation + ' ' + part  AS Permutation, Iteration + 1 AS Iteration
FROM Subsets s 
JOIN @NameParts n ON s.Permutation NOT LIKE '%' + n.part + '%'
)
SELECT STUFF(Permutation,1,1,'') As Perm 
FROM SUBSETS
WHERE Iteration = @tokencount