Jacob asked me to write some explanations regarding my solution for challenge 17. First I'd like to say, that this challenge is the only one I participated so far. Usually if I look at the problem and see the possible solutions, I then try to crack it, otherwise I may not even attempt to solve the problem, and that's why I didn't attempt to solve other challenges.
For this particular challenge it took me less than 20 minutes to solve it, but then I had to submit my solution several times because of my inattentiveness to details.
Let me remind my solution:
;with cte as (
c1.DATA, 0 as Level,
when c2.ID is null then c1.Data
' '+ c1.data + ' ', ' ' +
c2.keyword + ' ',' '+
'<a href="tsql.com?id=' +
CAST(c2.id as varchar(6)) +
'">' + c2.keyword + '</a>' + ' '
end as DataWithLink,
ISNULL(c2.ID, 0) as KeywordID,
c2.keyword as ReplacedWord
from @t c1
left join @t c2 on ' ' + c1.Data + ' ' like '% ' + c2.keyword + ' %'
c2.Level+1 as Level,
' '+ c2.DataWithLink + ' ', ' ' + c1.keyword +
' ',' ' + '<a href="tsql.com?id=' +
CAST(c1.id as varchar(6)) + '">' + c1.keyword +
'</a>' + ' '
) as DataWithLink,
c1.ID as KeywordID,
c1.keyword as ReplacedWord
from @t c1
inner join cte c2 on
' ' + c2.DataWithLink + ' ' like '% ' + c1.keyword + ' %'
and c1.id >c2.KeywordID
DataWithLink as Data
row_number() over (partition by ID order by Level DESC) as RowNum
) X where RowNum = 1 order by ID
There is nothing too complicated in this solution. One idea, I used, can be found in my blog How to search for all words inclusive without using Full Text search The idea of making sure we search a particular word inside the longer phrase is to add a space before and after the word and the same for the phrase. This idea is used in replace and in the inner join conditions in the recusrive CTE. I wrote about recusrive CTE in this blog CTE and hierarchical queries
So, this is all what it took. I already forgot why do I have an extra condition of c1.ID > c2.KeywordID, but probably it is necessary. And then to get our final solution we just select records with maximum level from this CTE.
Hopefully this explanation and especially the 'search' idea was useful.