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 (
	select 
		c1.ID, 
		c1.Keyword, 
		c1.DATA, 0 as Level, 
		case 
			when c2.ID is null then c1.Data 
			else 
				rtrim(
					ltrim(
						replace(
							' '+ 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 + ' %'
	union all
	select 
		c2.ID, 
		c2.Keyword, 
		c2.DATA, 
		c2.Level+1 as Level, 
		rtrim(
			ltrim(
				replace(
					' '+ 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
)
select 
	ID, 
	Keyword, 
	DataWithLink as Data 
from (
	select 
		ID, 
		Keyword, 
		DataWithLink, 
		row_number() over (partition by ID order by Level DESC) as RowNum 
	from cte
) 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.