How to change URLs embedded in a HTML macro through database

This article explains step by step instructions on identifying and changing the URLs embedded in Confluence through database.

Instructions

Search for URLs in the Confluence database:

1. Stop Confluence following your standard procedure.

2. Run a SQL statement to update the body content. An example is shown below:
   

   SQL query to identify the embedded URL's

   select c.CONTENTTYPE,c.TITLE, s.SPACENAME, s.SPACEKEY, s.SPACETYPE, s.SPACESTATUS 
   from content c join spaces s on c.SPACEID=s.SPACEID 
   where CONTENTID in 
   (select CONTENTID from bodycontent where BODY like '%<INSERT_KEYWORD_HERE>%')

3. Replace the <INSERT_KEYWORD_HERE> with your keyword. The '% ' symbol represents a wildcard search.

4. The SQL results return the CONTENTTYPE,TITLE with other space details including the SPACESTATUS (either CURRENT or ARCHIVED).

   Sample query :

   Example

   select c.CONTENTTYPE,c.TITLE, s.SPACENAME, s.SPACEKEY, s.SPACETYPE, s.SPACESTATUS from CONTENT c join SPACES s on c.SPACEID=s.SPACEID  where CONTENTID in (select CONTENTID from BODYCONTENT where BODY like '%https://www.w3schools.com/html/html_examples.asp%')

   Output:

Change URLs in the Confluence database:

1.   Run the query to change the URL links within the Confluence pages.

   update BODYCONTENT
   set body = replace(body, '<INSERT_OLDURL_HERE>', '<INSERT_newURL_HERE>')
   where body like '%<INSERT_OLDURL_HERE>%';

   Sample query :

   Example

   update BODYCONTENT set body = replace(body, 'http://help.websiteos.com/websiteos/example_of_a_simple_html_page.htm', 'https://www.w3schools.com/html/html_examples.asp')
   where body like '%http://help.websiteos.com/websiteos/example_of_a_simple_html_page.htm%';

2. Start Confluence following your standard procedure.
3. Validate the change of the URL links within the Confluence pages.