Help needed on this tricky query

Transact-SQL
1

I have a tricky and fascinating case and a constellation I can't refer to any query I've seen before. Anyone give me a hint of how to handle this?

I have this query that creates a column that is used to display a text with highlighted Keywords.The Keywords come from a table Keywords with the column KEYWORD

How do have to join the table with the query in order that any keyword is applied the Replace command to, so that the final text includes all html commands to highlighten all keywords that exist in the displayed text.

SELECT 
			   REPLACE(CAST(TEXT as nvarchar(max)), KEYWORD, '<span style=background-color:yellow>' + 'KEYWORD' + '</span>') as Display 
			   from  SALESLINE 
			   WHERE ITEMID = (SELECT ITEMID 
			                                           from SALESLINE 
			                                           where RECID = @RECID)

Martin

2

Actually it wasn't that tricky, but for some reason it took me a while to understand that this has to be done with a loop.

I followed this link:
(ups... can't post a second link (which one is the first one???) as I'm told, because I am a new user... (?) I'm user since 2008.... hmm doesn't make to much sense. Maybe since they changed the layout of this page.

Martin

3

Something like this perhaps:

create table #salesline (recid int,[text] varchar(max));
insert into #salesline (recid,[text])
 values (1,'This keyword is bold')
       ,(2,'This keyword is italic')
;
create table #highlight (word varchar(max),pretext varchar(max),posttext varchar(max));
insert into #highlight(word,pretext,posttext)
 values ('keyword','<span style=background-color:yellow>','</span>')
       ,('bold','<b>','</b>')
       ,('italic','<i>','</i>')
;

with cte1
  as (select *
            ,row_number() over(order by word) as rn
        from #highlight
     )
    ,cte2
  as (select a.recid
            ,replace(a.[text],b.word,b.pretext+b.word+b.posttext) as [text]
            ,b.rn as rn
        from #salesline as a
             left outer join cte1 as b
                          on b.rn=1
      union all
      select a.recid
            ,replace(a.[text],b.word,b.pretext+b.word+b.posttext) as [text]
            ,b.rn as rn
        from cte2 as a
             inner join cte1 as b
                     on b.rn=a.rn+1
     )
    ,cte3
  as (select recid
            ,[text]
            ,row_number() over(partition by recid order by rn desc) as rn
        from cte2
     )
select recid
      ,[text]
  from cte3
 where rn=1
;

drop table #highlight,#salesline;