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

  • Create a database backup before changing anything.
  • Additionally, any database modifications must be done while Confluence is shut down and not running.

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.