Otdfbt

Follow up of question

I have the following data:

I have the following sample data for permutations and combination.

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');

For an example expected result:

name
----------------
John Mak Sam
John Sam Mak
Mak John Sam
Mak Sam John
Sam John Mak
Sam Mak John
....
....

Tried the solution from previous question:

--To store Words 
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 * 
FROM splitdata;

Query for Permutation and Combination:

--Query for Permutation and Combination
;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,
 FullNameVariation
FROM
 permutations
WHERE
 Level = 1
ORDER BY
 ID,
 FullNameVariation 
OPTION (MAXRECURSION 5100);

Note: Above query works fine but unable to get following record in the result set:

Nasir Ahmed Amir Ahmed Mehendy

and that is because of this condition:

' ' + p.FullNameVariation + ' ' NOT LIKE '% ' + t.NamePart + ' %'

The word Ahmed is repeated and the above condition not met.

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]

You didn't mention number of rows return in output if Ahmed is repeated.

In my script, 74 rows are return. It count Ahmed as one.

If different Output is desire then show the Example of repeated word like 'Ahmed'

Any Split string function that also return row number.

CREATE FUNCTION [dbo].[DelimitedSplit2K]

 (@pString VARCHAR(2000), @pDelimiter CHAR(1))

RETURNS TABLE WITH SCHEMABINDING AS
 RETURN

 WITH E1(N) AS (
 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
 ), --10E+1 or 10 rows
 E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
 E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
 cteTally(N) AS (

 SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
 ),
cteStart(N1) AS (
 SELECT 1 UNION ALL
 SELECT t.N+1 FROM cteTally t WHERE SUBSTRING(@pString,t.N,1) = @pDelimiter
 ),
cteLen(N1,L1) AS(
 SELECT s.N1,
 ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000)
 FROM cteStart s
 )

 SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1),
 Item = SUBSTRING(@pString, l.N1, l.L1)
 FROM cteLen l
;

Number Table,

create table tblnumber(number int not null)
insert into tblnumber (number)
select ROW_NUMBER()over(order by a.number) from master..spt_values a
, master..spt_values b
CREATE unique clustered index CI_num on tblnumber(number)

Sample Data,

create table #tbltest
(
 id int identity(1,1),
 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');

The Scripts (WITHOUT RBAR),

create table #tbltest1
(
 id int identity(1,1),
 name varchar(50)
 ,GroupID int
 ,rownum int

);
insert into #tbltest1 (name,GroupID,rownum)
select item,t.id, ca.ItemNumber from #tbltest t
cross apply(select * from dbo.DelimitedSplit2K(t.name,' '))ca


DECLARE 
 @max integer = 
 POWER(
 (
 SELECT COUNT(*) 
 FROM #tbltest1 AS s
 )
 ,3
 ) ;


;With CTE as
(
SELECT s.id
 ,s.GroupID
 ,ltrim((select ' '+name from #tbltest1 t1 where t1.GroupID =s.groupid order by (CRYPT_GEN_RANDOM(c.n)) for xml PATH('') ))name
 FROM #tbltest1 AS s
CROSS APPLY (
select top (@max) number n FROM dbo.tblnumber 
)c

) 

select distinct name from CTE
--where groupid=1 


drop table #tbltest
drop table #tbltest1

How many rows will be process at one time ?

How many rows are there is table ?

Above all how many average word can be there in column Name