Try our conversational search powered by Generative AI!

Lee Crowe
Nov 19, 2014
(0 votes)

Content search and replace

The development team I currently work in like to have content in line with production content but it very quickly becomes outdated.

To aid with this we have Go pipelines which restore production database backups over lower internal environment databases.

The CMS Site has many links to various other product journeys. These journeys have environment specific Urls. If we are are in our regression environment, we would like content links to hand off to the relevant regression product journeys.

Our go pipeline restores the production database and performs many content replacements by executing a search and replace stored procedure. Below you will find the stored procedure definition to search and replace content.

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_CustomSearchAndReplace]') AND type in (N'P', N'PC'))
      DROP PROCEDURE [dbo].[usp_CustomSearchAndReplace]

CREATE PROCEDURE [dbo].[usp_CustomSearchAndReplace]
      @SearchFor nvarchar(1000),
      @ReplaceWith nvarchar(1000) = ''
      SELECT * FROM [tblContentProperty] WHERE [String] LIKE '%' + @SearchFor + '%' OR [LongString] LIKE '%' + @SearchFor + '%'
      SELECT * FROM [tblWorkContentProperty] WHERE [String] LIKE '%' + @SearchFor + '%' OR [LongString] LIKE '%' + @SearchFor + '%'

      IF LEN(LTRIM(RTRIM(@ReplaceWith))) > 0
            PRINT 'Updating published pages'
            UPDATE [tblContentProperty] SET [String] = REPLACE([String], @SearchFor, @ReplaceWith) WHERE [String] LIKE '%' + @SearchFor + '%'
            UPDATE [tblContentProperty] SET [LongString] = REPLACE(CAST([LongString] AS nvarchar(max)), @SearchFor, @ReplaceWith) WHERE [LongString] LIKE '%' + @SearchFor + '%'
            PRINT 'Updating unpublished pages'
            UPDATE [tblWorkContentProperty] SET [String] = REPLACE([String], @SearchFor, @ReplaceWith) WHERE [String] LIKE '%' + @SearchFor + '%'
            UPDATE [tblWorkContentProperty] SET [LongString] = REPLACE(CAST([LongString] AS nvarchar(max)), @SearchFor, @ReplaceWith) WHERE [LongString] LIKE '%' + @SearchFor + '%'
            PRINT 'Updating soft links'
            UPDATE [tblContentSoftlink] SET [LinkURL] = REPLACE([LinkURL], @SearchFor, @ReplaceWith) WHERE [LinkURL] LIKE '%' + @SearchFor + '%'
Nov 19, 2014


Nov 19, 2014 02:27 PM

The TSQL above is for EPiServer v7.

For EPiSever v6 the following replacements will need to be made:
- Replace [tblContentProperty] with [tblProperty]
- Replace [tblWorkContentProperty] with [tblWorkProperty]
- Replace [tblContentSoftlink] with [tblPageSoftlink]

Henrik Fransas
Henrik Fransas Nov 19, 2014 03:58 PM

Do not forget that you have to make EPiServer to recycle the cache (IIS reset is the best way)

Nov 19, 2014 04:20 PM

Indeed, our Go pipelines recycle the application pools once content has been refreshed.

Nov 20, 2014 03:17 PM

Nice work Croweman. as ever I'd approach this in a slighlty different way using my favourite tool IIS UrlRewrite:)

Rather than modifying the datbase. I've approached this by adding some outbound rewrite rules. These scan any outbound html from the server and replace all instances of the live url with the test site url (or other environment). The advantage of this per environment configuration approach, is no changes to data. and you can be very sure that no urls matching that pattern willl ever appear, regardless of how they are entered in the website.

Obviously scanning outbound html incurs a small performance hit, but on a test environment this shouldn't be an issue.

I've put a gist together of the type of configuration needed.

Nov 20, 2014 05:37 PM

Very nice Doctor ;)

I didn't realise you could modify outgoing html using the rewrite module :)

Please login to comment.
Latest blogs
Join the Content Recommendations Work Smarter webinar May 8th 16.00-16.45 CET with expert Aidan Swain

Learn more about Content Recommendations, with Optimizely’s very own Senior Solutions consultant, Aidan Swain . He will discuss best practices and...

Karen McDougall | Apr 12, 2024

Plugin for Identifying and Bulk Deleting Orphaned Missing Properties in Optimizely

I am aware that the Optimizely World community has extensively discussed this topic, with numerous solutions and code snippets available to help...

Adnan Zameer | Apr 11, 2024 | Syndicated blog

Enhancing the Authoring Experience: Extending the LinkItem

The LinkItem field is one of the most demanded properties by the community, allowing editors to effortlessly create and manage links across pages a...

Santiago Morla | Apr 10, 2024 | Syndicated blog

The distinctions between Optimizely Web Experimentation and Optimizely Feature Experimentation

This blog is part of the series - Unlocking the Power of Experimentation: A Marketer's Insight. Let’s dive into Optimizely's powerful experimentati...

Holly Quilter | Apr 9, 2024

Optimizely SaaS CMS: Balancing TCO and ROI in Your CMS Hosting Decision

With Optimizely SaaS CMS coming soon, I’ve been talking with companies about the tricky business of picking the right core system software for thei...

Johnny Mullaney | Apr 8, 2024 | Syndicated blog

What version of Optimizely CMS am I running?

Optimizely continually rolls out new features for CMS customers and these features are normally for the latest major version of Optimizely CMS (CMS...

David Knipe | Apr 8, 2024 | Syndicated blog