Permutation and Combination of words

Extending the original answer by Andriy M we can uniqify the strings at the insert phase with a dummy character (in my case I used ~), and remove that character later.

In this case I chose to use REPLICATE passing in the dummy character '~' and the ROW_NUMBER function for the number of instances to replicate. This should ensure that all strings are unique.

dbFiddle

Full code

create table tbltest
(
    name varchar(50),
    addres varchar(100)
);

insert into tbltest values('Sam Mak John','Street 1 HNo 101 USA');
insert into tbltest values('Donatella Nobatti','HNo 101 UK');
insert into tbltest values('Sam Buca','Main Road B Block UAE');
insert into tbltest values('Juan Soponatime','Hight Street CA');
insert into tbltest values('Aaron Spacemuseum','HNo A10 100 feet Road A Block ');
insert into tbltest values('Nasir Ahmed Amir Ahmed Mehendy','UAE High Street');
GO

CREATE TABLE dbo.NameParts
(
  ID int NOT NULL,
  NamePart varchar(30) NOT NULL
);

--Table variable to identity column for each words
DECLARE @Inter TABLE (id int identity(1,1), names varchar(max));

--Insert into table variable
INSERT INTO @Inter(names) SELECT name from TblTest;

--Query to insert into NameParts table
;WITH splitdata 
AS 
(
    SELECT id,splitname 
    FROM   
    (
        SELECT *,Cast('<X>' + Replace(F.Names, ' ', '</X><X>') + '</X>' AS XML) AS  xmlfilter 
        FROM   @Inter F
    )F1 
    CROSS apply 
    (
        SELECT fdata.d.value('.', 'varchar(50)') AS splitName 
        FROM   f1.xmlfilter.nodes('X') AS fdata(d)
    ) O
) 
INSERT INTO dbo.NameParts
SELECT id,[splitdata].[splitName] + REPLICATE('~',(ROW_NUMBER() OVER (PARTITION BY [splitdata].[id] ORDER BY [splitdata].[splitName] ASC))
FROM splitdata;
GO

;WITH
  permutations AS
  (
    SELECT
      ID = t.ID,
      FullNameVariation = CAST(t.NamePart AS varchar(500)),
      Level = COUNT(*) OVER (PARTITION BY t.ID)
    FROM
      dbo.NameParts AS t
    UNION ALL
    SELECT
      ID = t.ID,
      FullNameVariation = CAST(p.FullNameVariation + ' ' + t.NamePart AS varchar(500)),
      Level = p.Level - 1
    FROM
      dbo.NameParts AS t
      INNER JOIN permutations AS p ON t.ID = p.ID
    WHERE 1=1
      AND p.Level > 1
      AND ' ' + p.FullNameVariation + ' ' NOT LIKE '% ' + t.NamePart + ' %'
  )
SELECT
  ID,
  REPLACE(FullNameVariation,'~','')
FROM
  permutations
WHERE 1=1 AND
  Level = 1
ORDER BY
  ID,
  FullNameVariation 
OPTION (MAXRECURSION 5100);

DROP TABLE [dbo].[tbltest]
DROP TABLE [dbo].[NameParts]