Recursive Replace Multiple Tags




Create table article   (id int, title varchar(100), body varchar(4000)) 
insert into article values(1, 'Story1', 'At the same time there is a list consisting of: DUCHS, EUROC, GLSPE and WODST. Only two of the tags have covered with the prices in the last three months - GROSV at 99.11 on 8 October and JUBIL at 0s on 11 September.')

Create table tag_library (id int, name varchar(10), uploadDate date) 
insert into tag_library (id ,name) values( 1,'DRYDN33'), (2,'DUCHS'), (3,'DRYDN33'), (4,'DRYDN15'), (5,'EUROC'), (6,'DRYDN15'), (7,'GROSV')


;with mycte (i, title,  body ) as (
select 1 as i, title, body from article 
union all
Select m.i+1 as i, m.title,   cast(replace(m.body,tl.name,'<a href="pagename.aspx?tag='+tl.name+'">'+tl.name+'</a>') as varchar(4000)) body  
from mycte m inner join tag_library tl ON tl.id= m.i )

Select distinct title, body from mycte  m 
WHERE i = (SELECT MAX(i) FROM mycte c 
WHERE m.title = c.title)

drop table article, tag_library



 
Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s