Search and Replace – Text/HTML – DotNetNuke 5.5

I couldn’t get the Engage: F3 module to work with DotNetnuke 5.5 due to a known issue.  This issue will be fixed when Engage: F3 3.4 is released.  However, if you need a quick fix until then you can use this script based on the F3 module.

WARNING:  Backup your database before executing this script. The script may affect every item published by the Text/HTML module and you will want to have a backup in case you make a mistake. 

DECLARE @searchValue NVARCHAR(100)
DECLARE @replaceValue NVARCHAR(100)

SET @portalId = 0
SET @searchValue = '/website/'
SET @replaceValue = '/'

DECLARE @PublishedContent TABLE (ItemId int)
INSERT INTO @PublishedContent (ItemId)
SELECT ht.ItemId
FROM [HtmlText] ht
WHERE ht.IsPublished = 1 
  AND ht.LastModifiedOnDate = (SELECT MAX(LastModifiedOnDate) FROM [HtmlText] WHERE ModuleID = ht.ModuleID AND IsPublished = 1)
UPDATE [HtmlText]
SET Content = REPLACE(CAST(ht.Content AS NVARCHAR(MAX)), @searchValue, @replaceValue)
FROM [HtmlText] ht 
 JOIN @PublishedContent pc ON (ht.ItemID = pc.ItemId)
 JOIN [vw_Modules] m ON (m.ModuleID = ht.ModuleID)
 JOIN [vw_Tabs] t ON (t.TabID = m.TabID)
 JOIN [vw_Portals] p ON (p.PortalID = t.PortalID)
WHERE ht.Content COLLATE SQL_Latin1_General_CP1_CS_AS LIKE '%' + REPLACE(REPLACE(@searchValue, '\', '\\'), '%', '\%') + '%' ESCAPE '\'
  AND (@portalId IS NULL OR m.PortalID = @portalId)

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.