Generate permutations and filter

1

Hello

I have two lists of strings:
ABFG
BGER
GJRO
....

and

AR
GT
RP
....

Is it possible to find which two letters appear in a common string from the first set of strings but not in the second set?

In fact, the 'letters' are not just single letters but substrings which may complicate things. I may need to generate all permutations of such substrings by combining other strings.

Is there any function to generate the combinations and then check the concurrent appearance in the first set of strings but not the second?

Thanks!

2

using the sample data you provided please post the desired result from the sample data/

4

does this look like anything you are looking for

create data script

DECLARE @Str_Tbl1 TABLE(col_string VARCHAR(100));
INSERT @Str_Tbl1 VALUES('qwertyuiop');

DECLARE @Str_Tbl2 TABLE(col_string VARCHAR(100));
INSERT @Str_Tbl2 VALUES('sdqwgyuityjklop');

select 'String1',* from @Str_Tbl1
select 'String2',* from @Str_Tbl2

;WITH x AS 
(
  SELECT TOP (2048) n = ROW_NUMBER() OVER (ORDER BY Number) FROM master.dbo.spt_values ORDER BY Number
) , cte_str1 as 
(
SELECT 
    substring(f.col_string, x.n,2) as String1 
FROM 
   x, @Str_Tbl1 AS f
WHERE 
   len(substring(f.col_string, x.n,2)) = 2
),  cte_str2 as 
(
SELECT 
    substring(f.col_string, x.n,2) as String2
FROM 
   x, @Str_Tbl2 AS f
WHERE 
   len(substring(f.col_string, x.n,2)) = 2
)
select * from cte_str1 a join cte_str2 b on a.String1 = b.String2

image

1 Like