根据FirstName、LastName和MiddleName生成单词排列

Question

假设我有以下名称：John Smith Alexander。我想得到John Alexander Smith；Smith John Alexander；Alexander Smith John；etc.

中间名不必存在，因此从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

Answer 2

SELECT a.name, b.name, c.name 
FROM test a, test b, test c 
WHERE a.name != b.name 
  AND a.name != c.name 
  AND b.name != c.name;

适用于我的数据库，它不是tsql。当然，如果不修改2个或4个名称，它将无法工作，并且对于超过4个名称将无法维护。

对于像John Richard John这样的名称，它也不起作用。