merge two columns with similar words inner join

[rielyn]

I have two tables: books and newspapers. They have a pole. books_name and newspaper_titleI've brought them together as follows.

Select * FROM books b
  inner join newspaper n on b.books_name = n.newspaper_title

But I want to add inner jointo find similar words.

For example b.name Harry Potter and Azkaban and n.title Prince Harry waived title.

[amarantha]

If you could give me an example

CREATE TABLE t1 (id INT, txt VARCHAR(255));
CREATE TABLE t2 (id INT, txt VARCHAR(255));
INSERT INTO t1 VALUES (1, 'word1 word2 word3'), (2, 'word4 word5');
INSERT INTO t2 VALUES (123, 'word6 word5 word2'), (456, 'word3 word1');
GO

WITH 
cte1 AS ( SELECT t1.id, t11.value
          FROM t1
          CROSS APPLY STRING_SPLIT (t1.txt, ' ') t11 ),
cte2 AS ( SELECT t2.id, t21.value
          FROM t2
          CROSS APPLY STRING_SPLIT (t2.txt, ' ') t21 )
SELECT t1.txt t1_txt, t2.txt t2_txt, STRING_AGG(cte1.value, ',') common_words
FROM cte1
JOIN cte2 ON cte1.value = cte2.value
JOIN t1 ON t1.id = cte1.id
JOIN t2 ON t2.id = cte2.id
GROUP BY t1.txt, t2.txt;

t1_txt	t2_txt	common_words
word1 word2 word3	word3 word1	word1,word3
word1 word2 word3	word6 word5 word2	word2
word4 word5	word6 word5 word2	word5

db channel https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=101cfe460d434ba6414183a5dedff07e