Database Challenges

-- File Name: Naomi_Nosonovsky_tsqlChallenge_17_v3.sql
--Naomi_Nosonovsky_tsqlChallenge_17_v3.sql
-- recursive CTE 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)

-- Final output
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

Did you find something incorrect/wrong with this solution? Take a few seconds to Report It.

Did you understand how this solution work? If you find it difficult to understand, you can Request an Explanation or you can Write an explanation to help others better understand this solution.