Hello. I have to join two tables with common words.
I tried the possibilities, but I was advised to use CTE to split sentences to words and then to find each word in another record. But I don't have much knowledge of CTE. Is there anyone can help with this?
What I have tried:
DECLARE @tablea TABLE(Id int, [Text] VARCHAR(250))
DECLARE @tableb TABLE(Id int, Konto int, [Text] VARCHAR(250))
INSERT INTO @tablea(Id, [Text])
VALUES(1, 'Buy of Caffe'),
(2, 'Gebyr service'),
(3, 'Sell of goods'),
(4, 'Pbs salg varer'),
(5, 'Fatktura')
INSERT INTO @tableb(Id, Konto, [Text])
VALUES(1, 1001, 'Betaling for Faktura'),
(2, 1002, 'Buy goods from butik'),
(3, 1003, 'Sell of caffe to butik'),
(4, 1004, 'Salg'),
(5, 1003, 'GOOGLE service')
SELECT ta.Id, ta.[Text], tb.Konto
FROM @tablea ta LEFT JOIN @tableb tb ON PATINDEX('%' + tb.Text + '%', ta.Text) > 0